Sql-server – SQL CLR not properly enabling

64-bitsql serversql-server-2005sqlclr

We have a SQL server running SQL 2005 Workgroup 64 bit (9.0.4273), on Windows 2003 server 64 bit.

We have run sp_configure and reconfigured the server which indicates that the clr is now enabled.

exec sp_configure 'clr enabled', '1'
go
reconfigure
go

However, when trying to call CREATE ASSEMBLY the server completely dies on us and we have to do a full reboot of the machine.

A little more diagnostic information, even though clr enabled is set to 1 and we have rebooted the full server, running the following statement

select * from sys.dm_clr_properties

returns 

directory   
version 
state   locked CLR version with mscoree

which is what it says when the CLR is not enabled on another machine.

On a correctly enabled machine (after reboot) this function reads

directory   C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state   CLR is initialized

Best Answer

The query may not be display the CLR version properly if you don't have VIEW SERVER STATE permissions on the server.

Check your login that is running the query has VIEW SERVER STATE permissions. One method is grant that login 'db_owner' to the database and re-run the query, you should get the correct results then.

More info about sys.dm_clr_properties: [https://msdn.microsoft.com/en-nz/library/ms174983.aspx]

Related Topic