Enabling CLR integration in SQL Server 2005

Post date: Mar 4, 2011 7:26:37 AM

Introduction:

One of the new features in SQL Server 2005 is the feature to write your own stored procedure using any language supported in .NET. This feature gives you more flexibility to implement any complex logic in your business scenario.

Also, implement CLR (Common Language Runtime) stored procedures give you better performance if and only if your stored procedure contains complex logic which is not easy to implement using T-SQL stored procedure which we used to use in SQL 2000.

It's recommended by most of Microsoft SQl 2005 specialists to use CLR Stored procedure only on certain scenarios.

But in regular Database operations such as insert/update/delete and select, using ordinary T-Sql Stored procedure.

If you create a stored procedure project in VS 2005,and you deployed in SQL 2005,the CLR integration is not enabled by default you have to enable it in your SQL 2005 instance you are working on it.

We have 2 ways to enable CLR integration in SQL 2005.

Enable/Disable CLR Integration in SQL 2005 using T-SQL statement query:

If you try to execute CLR stored procedure you will get this error:

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To enable execution of .Net Code in SQL 2005, this is the script you need, run it in the running instance of SQL 2005; it will enable .net CLR code.

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO

To disable execution of .Net Code in SQL 2005, this is the script you need, run it in the running instance of SQL 2005; it will enable .net CLR code.

EXEC sp_configure 'clr enabled', 0;

RECONFIGURE WITH OVERRIDE;

GO

Enable/Disable CLR Integration in SQL 2005 using Surface area configuration:

The second way to enable .Net code in SQL 2005 stored procedures, is to use SQL Server Surface Area Configuration.

From Start-> programs -> SQL 2005->Configuration tools ->SQL Surface Area Configuration.

Click with Surface area configuration for features.

Just click on Enable CLR integration and click apply and ok.

Conclusion

Enable CLR integration to execute .net code inside SQL 2005, it's a simple task.

Enable this is important to check it after deploying your stored procedure and also important on production environment after deploying or restoring your Database on production server.