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

c# .net sql-server sqlclr
Kirupananthan.G.S picture Kirupananthan.G.S · Jul 31, 2014 · Viewed 13.3k times · Source

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

Solomon Rutzky picture Solomon Rutzky · Jul 31, 2014

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 either SQL Server 2012, 2014, or 2016.

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) (free registration is required by that site)

Related questions

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

I'm using threading in my SQLCLR Project and I'm getting the following error when I try to run my user defined function. What can I do to enable threading things such as the Parallel library and await/async in my …

Read More
Sending HTTP POST request from SQL Server 2012 or SQL CLR C#

Is there a generally accepted standard way for sending HTTP requests from SQL Server 2012? What I am trying to do is to use a remote server to process a search query and then insert the results back into the SQL …

Read More
Extracting a .NET Assembly from SQL Server 2005

I am trying to help a personal friend (who now also is a client) with a SQL CLR related problem. He has a SQL Server with a database that has 3 .NET assemblies embeded in it. He asked me to help …

Read More