How to convince sql server 2008r2 to use clr v4.0 instead of v2.0?

Question

I have sql server 2008r2. According to my internet research it supports .net framework 4.0. I tried to install my assembly with sql clr functions and receved an error.

CREATE ASSEMBLY for assembly 'MyAssembly' failed because the assembly is built for an unsupported version of the Common Language Runtime.

Query

select * from sys.dm_clr_properties

Gives result:

directory C:\Windows\Microsoft.NET\Framework64\v2.0.50727\

version v2.0.50727

CLR is initialized

I check C:\Windows\Microsoft.NET\Framework64\ to be sure and found v4.0.30319 folder where. So, .net v4.0 is installed.

So, I need to change CLR version that used for sql. I tried

sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

It did not help. I tried to add sqlservr.exe.config with content

<configuration>
   <startup>
      <requiredRuntime version="v4.0"/>
   </startup>
</configuration>

to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn folder and restart sql server. It didn't help as well.

I know about option with creating registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.NETFramework\OnlyUseLatestCLR and setting it to 1. It can broke other solutions, so I affraid of using it on production.

Is where any suggestions how to convince sql server to use clr v4.0?

So, the answer is - where is no way to do it.

In my case I lowed the target framework to 3.5 and excluded some clr functions.

Answer 1

You can't. In an article posted by Doug Holland in 2010 it is explained that older versions of SQL Server (up to and including 2008 R2) use the LockClrVersion call to restrict the .NET version that can be loaded to the latest 2.0 version.

To use .NET 4.0 you will have to use SQL Server 2012 and above