What is the SQL Server CLR Integration?

I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a vague error in the application and still didn’t know the exact cause. I fired up an XEvents session to find the query causing the issue and found a query failing with the following error:

Msg 6263, Level 16, State 1, Procedure dbo.MyProc, Line 60 [Batch Start Line 14]
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Do you recognize this reference to CLR? Let’s examine what we need to enable.

What is CLR?

The CLR (Common Language Runtime) integration must be enabled if you have .NET code that you want to run in SQL Server. This code could be objects like stored procedures, triggers, or functions. Since managed code compiles natively prior to execution, this can lead to better performance.

Considering what I knew about this particular project, it made sense that I was seeing the error.

Back to the error message above, it’s pretty straightforward in telling me what I need to do to fix my problem. Let’s follow its instructions.

Enabling CLR

To enable the CLR integration in SQL Server, we need to set the value of ‘clr enabled’ to 1. Let’s find out the current value. We can either run this query:

SELECT * FROM sys.configurations
WHERE name = 'clr enabled';
GO

Or this:

EXEC sp_configure 'clr enabled'

We see both config_value and run_value are set to 0. Let’s run the following to set ‘clr enabled’ to 1:

EXEC sp_configure 'clr enabled', 1

Are we all set? Not yet. We need to run RECONFIGURE to complete our changes and set the run_value to 1.

We can run:

RECONFIGURE

Or ideally we would have ran it all together like:

EXEC sp_configure 'clr enabled', 1
RECONFIGURE

Now we can check our current values again and see that we have enabled CLR successfully:

Easy Fix..This Time

Enabling CLR fixed my problem and that was that. It’s nice when the fix is that simple. If you’re interested in reading more, check out Microsoft’s pages on the CLR integration here.

Thanks for reading!