Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default.
This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR.
EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
3 Comments. Leave new
Does this still require a restart of the service? I’d like to add this to a DDL script and would like to know if it would work without restarting the machine? Thanks for this information
Brain Schumann,
In Older versions of SQL Server, yes this command needed a sql server instance restart. But From SQL Server 2005, if you use, RECONFIGURE WITH OVERRIDE, Restart is not needed.
For Ex:
EXEC sp_CONFIGURE ‘show advanced options’ , ‘1’;
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_CONFIGURE ‘show advanced options’ , ‘0’;
GO
RECONFIGURE WITH OVERRIDE
GO
Also, You can do the same thing through, Surface Area Configuration.
Start – All Programs – Microsoft SQL Server 2005 – Configuration Tools – Surface Area Configuration – Surface area Configuration for features
Click on the SQL Server instance, Expand Database Engine for that SQL Server instance and then point to CLR Integration on right side of panel, Check the radio button, Click Ok.
You are done. This will also not require a restart of SQL Server Instance.
Good if you test this out of your DEV/ TEST Env, before making change in production.
~ IM.
Hi Pinal,
Is the Exec with ‘advnced options’ mandatory???
I am asking because i just ran :
EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’
GO
RECONFIGURE;
GO
and everthing is fine…