sql server - 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:WindowsMicrosoft.NETFramework64v4.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

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_MACHINESOFTWAREMicrosoft.NETFrameworkOnlyUseLatestCLR

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

Hans