Wills Consulting LLC

One of the much-touted features of Microsoft SQL Server 2005 is the ability to host .NET assemblies inside a SQL database. This means that you can write stored procedures (or triggers or functions) in manager CLR code.

Visual Studio 2005 includes the SQL Server Project template to make it easy to write sprocs in C# or VB.Net. T-SQL was designed for set-based operations; it was never intended to do heavy lifting procedural work (like nested looping).

Anyways, today I found myself writing a CLR sproc and wishing that I could use the Data Access Application Block from Enterprise Library 2.0. In a SQL Server Project, you can only add references to assemblies already hosted in SQL Server. No problem, I thought, I'll just fire up SQL Server Management Studio and add the assemblies, one at a time, to the Master DB. (This was just an experiment. If it worked I would have written a script using the correct system procedures and stored that in my DB deployment project.)

So... which assembly to load first? Well, thanks to the PAG group's fantastic blogging, I knew that most assemblies had a dependency on Microsoft.Practices.EnterpriseLibrary.ObjectBuilder.dll, so I chose to add that assembly first.

Next, I tried Microsoft.Practices.EnterpriseLibrary.Common.dll.

Ok, so Common has a dependency on System.Management.dll. But that's part of the the BCL (base class library), so shouldn't it already be loaded into SQL Server? Turns out that only a few of the BCL assemblies are installed in SQL Server out-of-the-box.

At this point I decided to give up, since surely there is a good reason why System.Management is not included. However, just for fun, I tried to add C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll.

Sounds serious.

Has anyone had any luck using EntLib to simplify their CLR stored procedure development? For that matter, has anyone besides me even had any interest in doing this? Is there a simpler approach that I'm overlooking?