What is CLR, How to Check If It is Enabled and How to Enable It

CLR stands for Common Language Runtime and is a technology developed by Microsoft that allows you to use managed .NET code in SQL Server environment. In other words, you can write your stored procedures, triggers and user-defined functions, aggregates and types in a .NET language. In order to do so, you need to create a class library project that contains them, import your assembly to your SQL server and tie it all together.

The technology is available starting with Microsoft SQL Server 2005. Here is your first steps:

First, verify that CLR is enabled on the system:

SELECT * FROM sys.configurations WHERE name = "clr enabled"

The sys.configurations table is a system table that contains your global SQL Server configuration properties. This statement will return either 0 or 1, indicating whether CLR is enabled. If CLR is disabled, you can enable it by running the following statement:

sp_configure 'show_advanced_options', '1'
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

The sp_configure stored procedure configures system options. Because CLR is an advanced option, we need to enable advanced options first. The RECONFIGURE statement is needed to actually update the value – think of it as a COMMIT on your configuration changes. Also note that it may not always update the configuration right away, according to Microsoft. So now, all that’s left is to change the clr enabled value to 1 to enable it and we are done!

Your next step is to Create a CLR assembly and a .NET SQL function (C#)!

This entry was posted in MSSQL and tagged , , , , , , . Bookmark the permalink.

One Response to What is CLR, How to Check If It is Enabled and How to Enable It

  1. Pingback: Create a SQL CLR Stored Procedure Using .NET (C# Example) | ℓūfħer Ṭecħᵒ

Leave a Reply

Your email address will not be published. Required fields are marked *