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.