Enabling CLR in SQL Server 2005

Posted by System | Tuesday, April 07, 2009

Although Microsoft has always advised us not to use them, if you are like me you still might have found reasons in the past to use one or more of the SQL Server 2000 undocumented features. In particular, I built a number of processes that used an undocumented, extended stored procedure (SP) called xp_getfiledetails, which allowed me to obtain various bits of operating system information for a physical disk file.

Well, with the release of SQL Server 2005, the "bad" news was that Microsoft decided to remove some of those undocumented features, including the xp_getfiledetails stored procedure. On the plus side, SQL 2005 at the same time brought with it a lot of new features, one of which is the Common Language Runtime (CLR). The CLR allows you to extend SQL Server functionality by building your own routines using programming languages other than T-SQL.

Just like most developers, I was a little surprised to discover recently that CLR Integration was disabled by within the default SQL Server configuration. After trawling through countless options of the Surface Area Configuration, I eventually found the relevant option to enable this feature, but for those of you who are short of time, I have since discovered that this can also be achieved the good old fashioned T-SQL way....

EXEC sp_configure 'show advanced options' ,'1';
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled' ,'1'
GO
RECONFIGURE
GO

You may need to restart your SQL Services - but from here you can create User Define Types (UDF), User Defined Functions (UDF), Table value Functions (TVF), Triggers, and Stored Procedures using programming language such as VB.NET or C# and deploy them to your SQL Server.