Tips and Tricks for:  SQL Server

Problem: In SQL Server 2005 the CLR is off by default. How do you enable it?

In an effort to improve security Microsoft has turned many features "off by default".  This is a big change from the old policy of turning every feature on so that developers weren't discouraged from using the feature due to difficulties in getting the feature to work.

Solution: Enable the server option 'clr enabled'

The script is pretty simple.  Here it is:

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go

Once you have the CLR enabled, you can write user-defined functions, triggers, stored procedures, user-defined aggregates, and user-defined types in VB.Net and C#.