September 2004 - Posts

In Bob's post - SQLCLR - List of approved framework class libraries, he discusses how he found, in an unusual way, the list of allowed framework libraries (which BTW is not 100% correct - see below), and subsequently a shortcoming in the VS IDE. 

So what he found out was that when you use the SQL Server project type to create SQLCLR assemblies - you can only add references to the allowed system dlls or other SQL Server project dll's . You can NOT add references to, for example, ordinary class library project dll's. That sux big time, and that's why I rarely use the SQL Server Project type when developing against SQL Server 2005!!

However the other day I came across a way around the problem:

  • Open Object Browser (View | Object Browser or Ctrl+Alt+J), and Browse for the component(s) you are interested in.
  • In Solution Explorer select the project you want to add the references to.
  • Finally, for each of the dll's you are interested in, in Object Explorer; click on the "Add to References in Selected Project in Solution Explorer" icon on the Object Browser tool-bar.

That should do it. I really hope this will be fixed in a future release of the IDE.

Oh, and finally: even though you may think that all the system dll's you can see in the "Add References" dialog for a SQL Server Project are on the allowed list; they are not! System.Runtime.Remoting.dll can be added, but SQL Server does not allow that dll to be registered.

In the Whidbey release of ADO.NET is the SqlDependency class. This class allows the user to set up a callback which is called when the underlying data changes. Bob wrote about it here.

Even if the "rules" Bob stated are followed you may not get it to work, and there are two main culprits for this:

  • Guest user has to be granted send rights on the SqlQueryNotificationService in the MSDB database.

This is as the dependency class under the covers uses Service Broker and posts a message to the  SqlQueryNotificationService when changes happen. Syntax to grant send rights are (in the MSDB database):

GRANT SEND ON SERVICE::SqlQueryNotificationService to GUEST

The second thing is:

  • if you try this in the AdventureWorks database it won't work because Service Broker is not enabled by default (however if you create a new database, SSB is enabled).

You can check whether SSB is enabled by using the DATABASEPROPERTYEX function like so:

select databasepropertyex('AdventureWorks', 'IsBrokerEnabled')

It returns 1 for true and 0 for false. To enable SSB you run:


I believe that's it. Have Fun!!

[Chris Sells] Windows Command Shell Partner Drop 3 Available

For Windows Command Shell (aka MSH aka Monad) users, Partner Drop #3 was just posted today on So far, all I know is that it fixes 45 internally and externally reported bugs, but it's nice to know that things are moving along on the first new shell we've had since NT 3.1.

In addition to the fixes Chris mentions, it also now runs on Whidbey Beta 1 (40607.42)! If you are into shell programming, or just want something more powerful; this is a must have!

Recently there has been some discussions about SQL Server attributes in the beta newsgroups for SQL Server 2005 and VS 2005. Having tried to answer them, I thought it might be a good idea to write a blog entry about it as well.

When you create a new SQL Server project in VS 2005, you can add items for Stored Procedures User Defined Functions, Triggers. User Defined Types and User Defined Aggregates. When you add, for example, a stored procedure you get some skeleton code looking something like this,

public static void YourProc() {
// Put your code here

where SqlProcedure is the attribute. These attributes are used by VS 2005 during deployment and by SQL Server at runtime. The discussions in the newsgroups has been about which attributes are used when. The available attributes are:

  • SqlProcedure
  • SqlTrigger
  • SqlFunction
  • SqlUserDefinedType
  • SqlUserDefinedAggregate
  • SqlMethod
  • SqlFacet

The differences and they way they are used is (as far as I can tell) as follows:

SqlProcedure and SqlTrigger are used only at deployment time by VS (and third party tools like my Yukon build task).

SqlFunction is used both during deployment as well as runtime. At runtime it indicates if the function is doing any data access and also tells SQL Server if the function is deterministic and/or precise.

SqlUserDefinedType and SqlUserDefinedAggregate is used at deployment time as well as runtime.

SqlMethod is used only at runtime, and can only be used by instance methods in a User Defined Type to indicate whether the method is doing data access or not. It inherits from SqlFunction, but you can not apply it to a method which is being used as a User Defined Function.

SqlFacet finally is only used during deployment and in VS it can only be used to indicate the return type of a UDT expression. In my Yukon Build Task however, it is used to indicate parameter types for any type of method as well as well as return types for functions, regardless if it is a UDT or not.

You who were around during Beta 1 of SQL 2005 and the Alpha of Whidbey may remember the SqlFunc attribute and wonder where that went. That was a pure VS 2005 thing and was used when deploying User Defined Functions. In Beta 2 of SQL and Beta 1 of Whidbey it was replaced by the SqlFunction attribute.

[Keith Brown] The book ships

My copy arrived on Saturday.

I even managed to get our logo down there in the bottom right corner :-D

Keith is done!! Congratulations Keith!


Microsoft provides Office source code to governments

Facing growing competition from open source software providers, Microsoft Corp. has decided to allow governments and international organizations access to source code for its Office 2003 productivity suite.

Wow, if this is true...

So I'm back again (at least for a little while). It seems that my blogging goes in cycles - a couple of weeks with fairly regular postings and then nothing for a month or two. I really need to put more effort into it. I so wonder how Scoble manages to churn out as much as he does, and still have a family life?

BTW, his post about getting recognised in "blog-land" is really interesting. Let's see if he is right?!

Even if I haven't blogged lately I have tried to read my subscribed blogs regularly and one blog that continuously comes up with good stuff is Junfeng Zhang's blog. He is from Microsoft and posts a lot about Assemblies and how they work in the CLR. Really good stuff! Speaking of MS bloggers and CLR goo - Dino continues his tutorial about hosting the CLR.

Finally, a belated Happy Birthday to myself.