SQLCLR using the wrong version of the .NET Framework

Question

During a recent restart of our development server the SQL Server started using .NET 4.0 for the SQLCLR. This means that nothing using the CLR in SQL works, or at least that's my understanding by reading these sources:

http://software.intel.com/en-us/blogs/2009/10/16/sql-server-2008-sqlclr-net-framework-version/

www.sqlskills.com/BLOGS/BOBB/post/On-SQL-Server-and-NET-40.aspx

All we get are error messages of this type:

Msg 6517, Level 16, State 1, Line 1
Failed to create AppDomain "xxx.dbo[ddl].3". 
Method's type signature is not Interop compatible.

Running the statement (as suggested by @john-christensen)

select * from sys.dm_clr_properties

results in the following information:

*Name*      *Value*
directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version     v4.0.30319
state       CLR is initialized

Does anyone know how to solve this or how we can force SQL Server CLR to use an earlier version of the Framework?

Answer 1

They made explicit choices to ensure that installation of .NET 4.0 would be non-impactful. It shouldn't be using .NET 4.0 or any of the new files except for the new shim files, mscoree.dll and mscoreei.dll. Those should be backward compatible with the 2.0 runtime. You could run Process Explorer to see the version numbers of the dll's loaded to verify that it is running the right runtime.

Answer 2

From the article and my research on the web, it looks like the opposite might be happening - could you potentially be registering a 4.0 DLL? It appears that SQL Server 2008 will always load the 2.0 CLR and not the 4.0 CLR. Try running this statement, it will tell you what version your SQL server is running:

select * from sys.dm_clr_properties

Answer 3

In your post at Intel that you reference, if you read it closely, it says:

SQL Server 2008 and the forthcoming SQL Server 2008 R2 release, previously codenamed "Kilimanjaro", will both continue to load the latest service release of the version 2.0 CLR.

And later on:

While future versions of SQL Server may load newer versions of the CLR, or even support the loading of multiple CLR's within the process, version 2.0 of the CLR is here to stay for SQLCLR within SQL Server 2008 and SQL Server 2008 R2.

I don't know how you manage to get something like .NET 4 loaded inside SQL Server 2008 R2....

Answer 4

Typically you can force a .NET application to use a specific .NET Framework version by specifying the supportedRuntime tag in the application's config file.

So you could try creating a sqlservr.exe.config in the \Binn folder under the root path of the SQL instance and specify there that you would like to use only .NET versions up to 3.5. Check this MSDN link for the structure of the config file.

Answer 5

Hi,

I experienced the same annoying problem. None of the Geography/Geometry stuff in my database worked. Took me some unsuccesfull reinstalls of SQL server to finally (some weeks later!) find the following key in my registry had been set to '1'

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\OnlyUseLatestCLR

when I reset it to '0', and rebooted the machine, things worked again!

Hans