SQL/VS 2005 February CTP Code Changes

I've been playing with the February CTP of both Visual Studio 2005 and SQL Server 2005 this weekend.  One of my exercises for the day was trying to get a Visual Studio SQL Server CLR project that I wrote for SQL 2005 Beta 2/VS 2005 Beta 1 to compile.  The project has a stored procedure, a UDT, and a stubbed out function, aggregate and trigger.  I couldn't get this to compile in the VS December CTP, but I was determined to get it to compile in the new February CTP.

I got it to work, though not easily.  I found that some of my code was using constructs that are now deprecated (that was fast!) and I also encountered a few compiler and deployment errors.  In reverse-engineering the latter, I discovered that CLR UDTs and Aggregates now must be implemented as structures, rather than as classes.  With hindsight this makes sense but it's a rather stark change...now (it would appear) you can no longer store "objects" in the database.  Of course, nothing stops you from serializing a .NET object as XML and storing it in an XML column, but that's another story.

Here are a few other changes I "discovered" (I use quotes because these changes are in fact documented on other SQL Server-oriented blogs like Bob Beauchemin's and Wally McClure’s and that's how I worked my way through them):

• The SqlContext.GetPipe method is replaced by the SqlContext.Pipe property.  This is extremely important because it is through the returned SqlPipe object that you can send data and text back to a client from a CLR stored procedure.
• Likewise, the SqlContext.GetConnection method is replaced by SqlContext.Connection property
• The SqlConnection.CreateCommand method is effectively replaced by SqlContext.CreateCommand method (making less necessary the use of the SqlContext.Connection property, described above) 

I also had the darnedest time getting even the stubbed out trigger to deploy.  Since my CLR project points to the AdventureWorks database, I defined the code as a FOR UPDATE trigger on the Production.Product table, using the SqlTrigger attribute and it just wouldn’t work.  As best I can tell, this attribute will not work on a namespaced table!  When I pointed the trigger to the non-namespaced “DatabaseLog” table, everything worked fine.  Since that’s not a table I would want to create my own trigger on, I worked around to the problem.  I removed the SqlTrigger attribute altogether, compiling the code as a plain VB Sub (function in C#), then after deploying the assembly, executed the following T-SQL code:

Create Trigger trgInsertTest

On Production.Product

For Update

As External Name [SSProjTestFebCTP].[SSProjTestFebCTP.Triggers].[trgInsertTest]

If anyone knows how to get the SqTrigger attribute to work with a namespaced table, I’d sure like to know.