SQL Server: How to check if CLR is enabled?

Question

SQL Server 2008 - What is an easy way to check if clr is enabled?

score 149 · Accepted Answer · edited Jul 08 '12 at 12:41

149

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

edited Jul 08 '12 at 12:41

answered Jan 26 '11 at 13:08

Jason

4,557
5
31
40

31

Additional Note : Value will be 1 if enabled, or 0 if disabled. – Muthukumar May 10 '14 at 17:45
This is still valid in SQL 2016. – Rashack Apr 16 '19 at 07:06

score 38 · Answer 2 · answered Jan 26 '11 at 13:10

38

Check the config_value in the results of sp_configure

You can enable CLR by running the following:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

MSDN Article on enabling CLR

MSDN Article on sp_configure

answered Jan 26 '11 at 13:10

codingbadger

42,678
13
95
110

6

I don't think you should actually configure the option just to check if it's configured! I realize it will say `was 0 now 1`, or similar, but ... – Josh M. Apr 17 '14 at 20:11

score 29 · Answer 3 · answered Oct 22 '13 at 19:29

29

The accepted answer needs a little clarification. The row will be there if CLR is enabled or disabled. Value will be 1 if enabled, or 0 if disabled.

I use this script to enable on a server, if the option is disabled:

if not exists(
    SELECT value
    FROM sys.configurations
    WHERE name = 'clr enabled'
     and value = 1
)
begin
    exec sp_configure @configname=clr_enabled, @configvalue=1
    reconfigure
end

answered Oct 22 '13 at 19:29

Larry Smith

1,863
16
15

1

EXISTS() is slightly faster than NOT EXISTS(). Just a friendly note. ;) – clifton_h Apr 26 '17 at 14:02
4

in this case imho readability trumps performance, which for a single shot dba usage like this would be infinitesimal. – Larry Smith May 10 '17 at 17:33

score 3 · Answer 4 · answered Jan 26 '11 at 13:11

3

select *
from sys.configurations
where name = 'clr enabled'

answered Jan 26 '11 at 13:11

grapefruitmoon

2,988
1
22
26

score 3 · Answer 5 · edited Sep 06 '18 at 18:36

3

The correct result for me with SQL Server 2017:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

USE <DATABASE>
GO

EXEC sp_changedbowner 'sa'
USE <DATABASE>
GO

ALTER DATABASE <DATABASE> SET TRUSTWORTHY ON;

From An error occurred in the Microsoft .NET Framework while trying to load assembly id 65675

edited Sep 06 '18 at 18:36

halfer

19,824
17
99
186

answered Apr 01 '18 at 18:20

Sayed Abolfazl Fatemi

3,678
3
36
48

score 2 · Answer 6 · answered Jun 11 '19 at 22:41

This is @Jason's answer but with simplified output

SELECT name, CASE WHEN value = 1 THEN 'YES' ELSE 'NO' END AS 'Enabled'
FROM sys.configurations WHERE name = 'clr enabled'

The above returns the following:

| name        | Enabled |
-------------------------
| clr enabled | YES     |

Tested on SQL Server 2017

SQL Server: How to check if CLR is enabled?

6 Answers6

Linked