Visual Studio 2010 and SQLCLR 2008 new features

There's a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were told that support would be forthcoming in the next version of Visual Studio for these features.

The problem with features not working with autodeploy or not being supported in "SQLCLR projects" is that programmers don't think the feature actually exists. I'd get blank stares (or arguments) when I talked about SQLCLR using nullable types or System.Core (LINQ to objects) being supported in SQL Server 2008. Because VS projects didn't support these things.

Visual Studio 2010 was released a few weeks ago, and I decided to revisit the SQLCLR code I'd written (all written as "ClassLibrary Projects" with DDL-based deployment) to see how they did with support of new features. Here's the list.

System.Core and System.Xml.Linq should appear in the "supported" library list (add reference) – works
Large User-Defined Aggregates and User-Defined Types (ie, MaxSize=-1 in SqlAggregate/SqlUserDefinedType attribute) – works
SqlGeometry/SqlGeography/SqlHierarchyId recognized as parameters/return values – works
SQLCLR support of .NET Nullable Types as parameters/return values – fails deployment
Multi-parameter user-defined aggregates – fails deployment
Ordered TVF – no SqlFunction property to generate correct DDL for these – can't be specified

That's not a great record. Guess I'll still be explaining that some SQLCLR features really can't be built in autodeploy projects.

My friend Adam Mechanic has a post about his experiences (in general) when doing development for SQLCLR in VS2010, which spurred me to test these things out. They are similar to mine, except that I don't think that post-deployment scripts make up for proper autodeploy support, and in most cases aren't enough to recover a failed autodeploy. Another irritation is the general error you get with some of the autodeploy features the don't work (like multi-parameter UDAggs) is sort of misleading… "The assembly failed verification". "Failed verification" is a specific error in SQLCLR, meaning that the assembly is using the wrong version of .NET or the PEHeader is hosed or you're using undefined .NET opcodes. Using this terminology threw me for a while, until I tried using CREATE ASSEMBLY DDL and noticed the assembly passed verification just fine.

I noticed one additional change. This is something that's never been officially supported (that I'm aware of) but worked in VS2005 and VS2008. It's the ability to do "Attach To Process" type debugging in SQLCLR (that is, attach to sqlservr.exe, run a script in SSMS and have the debugger hit the breakpoint). This doesn't seem to work in VS2010, and was my debugging method of last resort when dealing with complex scripts (that VS "Test Scripts" couldn't handle). Or debugging Service Broker activaton programs written in SQLCLR (when "F5 debugging" didn't work). Sigh…maybe I haven't yet discovered the secret sauce that enables this in VS2010.

One thing I haven't tried yet is targeting a SQL Server 2005 (where the new features aren't supported) with a VS2010 project. I'd hope, for example, that System.Core and System.Linq.Xml won't appear in the list of supported assemblies. But that's a test for another day.