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.

Sarnen

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)

Jurkoic

I am fine with the above... Is sql server 2012 and 2014 have the backwards compatibility for CLr 2.0? Please advise. thanks in advance. – Sarnen

Yes. Methods that work in 2.0 should work in 4.0 and above. Just check out my SQL# project: SQLsharp.com which is compiled against 2.0 and yet works just fine in SQL Server 2012. – Jurkoic

thanks it works. I have create another CLR with System.Data.Linq.. It show the below problem please advise..Assembly 'ElasticSearchCLR' references assembly 'system.data.linq, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request. – Sarnen

@Kirupananthan.G.S: that is a separate question entirely, but it is also a duplicate which has been answered here: #1189905 (read the accepted answer, the the link in that answer, and the comments on that answer). – Jurkoic

Very precise answer. Loved it! – Garfield

Answer 2

The bad news is you can't do that. SQL 2008 supports V2.0

Pate

But i can able to create assembly using the .net framework 3.5... how this is possible... Please help me on the above – Sarnen

This is the version of CLR, not the framework. Framework versions 2.0-3.5 contain CLR V2.0. – Pate

Take a look at this: msdn.microsoft.com/en-us/library/bb822049%28v=vs.110%29.aspx – Pate

i have the following sql server verison. Microsoft SQL Server 2008 R2 (SP2). i have seen the page link sent by you. .Net version 4 and CLR version 4 column have the SQL SERVER 2008 R2.. If i am anything wrong means Please advise. – Sarnen

The link I put above was to tell you the CLR version each framework version contains. And that is Windows Server 2008 R2 there. Anyway SQL Server versions prior to 2012 are locked to load CLR 2.0. To load CLR 4.0 you should use SQL-Sever 2012 or above :) – Pate

Is SQL server 2012 and 2014 have the backwards compatibility to run CLR 2.0? Please advise – Sarnen

I'm not sure about that, and I can't test it because I have 2008 R2 installed. But I think that would be possible – Pate

Answer 3

Set the target framework to 2.0 and then try again.

Pilgrimage