Failed to CREATE AN ASSEMBLY in SQL

Question

I've already dived into SQL clr programming. Unfortunately my first attempt is troubled. My C# assembly code is just so:

enter code here
public partial class FirstCLRRoutines
{
    public static int GetCLRFrameworkMajorVersion()
    {
        return System.Environment.Version.Major;
    }
}

And SQL code is:

USE master
GO
CREATE ASSEMBLY [Chapter2.FirstCLRRoutine]
FROM 'D:\projeler\SQL_CLR\SQL_CLR\bin\Debug\SQL_CLR.dll'

But I get this error message from MSSMSE:

Msg 6218, Level 16, State 3, Line 1
CREATE ASSEMBLY for assembly 'SQL_CLR' failed because assembly 'SQL_CLR' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

Answer 1

I just encountered exactly the same problem.

This is an old page, but first, formost and finally, the DLL must be built with .NET 2.0. SqlServer 2005 is built with .net 2.0, because that is the latest available when it was written. SqlServer 2008 might also have the same issue.

The DLL and the SqlServer must both reference the same .NET framekwork version. Once I figured this out, I created a separate project under 2.0 and shazam! worked perfect first time.

To see what version of .net your sql server uses:

select * from sys.dm_clr_properties

Answer 2

The accepted answer, while seeming to resolve the O.P.'s issue, is only partially correct and presents an overly simplistic explanation of the underlying cause, which could lead other people with a similar problem in the wrong direction.

The problem with the accepted answer is a misunderstanding of the .NET environment, and that same misunderstanding can also be seen in the Question itself. Within .NET, the CLR and the Framework are two separate things, each with their own versions.

The CLR (Common Language Runtime) is what executes managed code. This is not updated nearly as often as the Framework. The .NET Framework is a collection of libraries that provide the basic means of interacting with a particular version of the CLR.

A single version of the CLR typically has multiple versions of the Framework that it works with. A single version of the Framework, however, only works with one specific version of the CLR. For example, CLR version 2.0 works with Framework version 2.0, 3.0, and 3.5, while CLR version 4.0 works with all of the 4.x versions of the .NET Framework (i.e. 4.0, 4.5, 4.5.1, 4.5.2, 4.6, etc). To see the chart of CLR verion to Framework version relationships, see the MSDN page for .NET Framework Versions and Dependencies.

With regards to SQLCLR code, SQL Server only works with a single version of the CLR, and the specific version depends upon the version of SQL Server. SQL Server 2005, 2008, and 2008 R2 work only with CLR version 2. Since CLR version 2 only works with .NET Framework versions 2.0, 3.0, and 3.5, this means that SQL Server 2005, 2008, and 2008 R2 only work with .NET Framework versions 2.0, 3.0, and 3.5. Of course, SQL Server 2005 only included .NET Framework version 2.0 so there are a couple of newer libraries in .NET Framework version 3.0 and 3.5 that don't work in SQL Server 2005 without manually importing them (i.e. System.Core and System.Xml.Linq). Along those same lines, SQL Server 2012, 2014, and 2016 are statically linked to CLR version 4, which works with .NET Framework versions 4.0, 4.5, 4.5.1, 4.5.2, 4.6.

With regards to the information returned from both System.Environment.Version (in the Question) and sys.dm_clr_properties.version (in the accepted answer), they are reporting the CLR version, not the Framework version. So be careful not to confuse those two things reporting 2.0 or 4.0 as meaning you can only use Framework version 2.0 or 4.0.

And fortunately, due to backwards compatibility, code compiled against the CLR 2 Framework versions (2.0, 3.0, and 3.5) will run without needing to be recompiled in SQL Server 2012 and newer, even though they are on CLR version 4.

So, you generally cannot go wrong with using a Target Framework Version of 2.0, but you most certainly can use Framework versions beyond 2.0.

For a more in-depth look at developing SQLCLR code, check out the following article (and the series in general), which I wrote:

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

Answer 3

USE master 
GO 
CREATE ASSEMBLY [Chapter2.FirstCLRRoutine] 
FROM 'D:\projeler\SQL_CLR\SQL_CLR\bin\Debug\SQL_CLR.dll' 
WITH PERMISSION_SET = UNSAFE

Try that, and let me know if that works.

Answer 4

Short answer : set Sql Server version and .Net Framework version on project property.

First of all you have to check set your project property. in project property set the version of sql server that you want to create CLR for it. then choose .Net Framework version . for example if you want to create CLR for SQL Server 2008 you have to set .Net Framework to 3.5 and for 2005 choose .Net 2.0. i hope this solution help you.

Answer 5

The library, System.Environment, is not supported for CLR: http://msdn.microsoft.com/en-us/library/ms403279.aspx

You can still use it, as indicated in the "Unsupported Libraries" section of the article above, but keep in mind that the code has not been tested for security and reliability. This can cause unpredictable results in a production environment, so think about the risks and carefully test before deploying.

Also, I believe it either has to have a strong name or be deployed to a database marked as "Trustworthy" before it will execute.