Create CLR stored procedure using the dll created by .net framework 4.0 in sql server 2008. Is shows error

Question

I am using the below code for CLR stored procedure creation. While I am creating the assembly. it shows the below issue. My target framework is 4.0. sql server is 2008 r2

SQL code:

create assembly SampleSearch from 'E:\CLR Files\Sample\ElasticSearch.dll'

error message:

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

Answer 1

Microsoft SQL Server does not allow for mixed-mode CLR. Meaning, it is statically linked to a particular version of the CLR (which is not the same thing as the .NET Framework that most people confuse it for). SQL Server 2005, 2008, and 2008 R2 are linked to CLR version 2.0 which handles .NET Framework versions 2.0, 3.0, and 3.5, while SQL Server 2012 and 2014 are linked to CLR version 4.0 which handles .NET Framework versions 4.0, 4.5.x, 4.6.x, etc.

You can either:

• Recompile using a lower framework version, but if you are using functionality that started in .NET Framework version 4.0 or above then that won't work. Using .NET Framework 2.0 is always the safest bet for SQL Server 2005 - 2008 R2. If you need to use .NET Framework 3.0 or 3.5 for functionality that is not in 2.0 (and not in the list of supported .NET Framework libraries), then you will need to also register the appropriate .NET Framework 3.0 / 3.5 DLL in SQL Server as UNSAFE, and doing that requires setting the database option for TRUSTWORTHY to ON (which is best left as OFF if at all possible).
• Upgrade to SQL Server 2012 (or newer).

For more detailed info on the topic of .NET nuances within SQL Server (i.e. SQLCLR), please see the following article that I wrote on SQL Server Central, if not the entire series:

Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)