asked
CLR Assembly wont register with SQL Server
I am making a voyage into the unknown and working with SQL Server CLR for the first time. I have built a DLL but when I run CREATE ASSEMBLY FROM 'C:\Users\...\Visual Studio 2010\Projects\...\bin\Debug\MyDLL.dll' I get an error CREATE ASSEMBLY for assembly failed because assembly 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 I am trying to install this on a 2008 Developer Edition instance. When I check the VS project references there are lots (System, System.Core,System.Data etc) that point to .NETFramework v4.0. Is this an issue? How do I change them? I have tried the `CREATE ASSEMBLY` with `WITH PERMISSION_SET = EXTERNAL_ACCESS` but that has made no difference. The VS project is a DLL project, not a SQL CLR project as when I tried that type I couldnt use IMPORTS System.DirectoryServices in the module. Any/all advice and pointers to good reference materials welcomed.
Yes, the problem is with the .Net version. On the Project menu, click ProjectName Properties, on application tab downgrade the Target Framework to 3.0 OR 2.0. I am not sure with which version it would be compatible with.
There are a few issues here, only one of which was addressed in the accepted answer: 1. "CREATE ASSEMBLY for assembly failed because assembly failed verification." The main error is due to some code within your DLL that is attempting something that is not allowed in an assembly that is marked as either SAFE or EXTERNAL_ACCESS. Verification only happens in those two levels, but not in UNSAFE. 2. "The VS project is a DLL project, not a SQL CLR project as when I tried that type I couldnt use IMPORTS System.DirectoryServices in the module." This points to at least one thing being done in your DLL that will require it to be set to UNSAFE. There is a very limited number of libraries that have been certified as "guaranteed to work" in the CLR that is hosted within SQL Server, and **System.DirectoryServices** is not one of them. So this will require that you manually load that assembly as UNSAFE, and referencing that library will require that you DLL also be set to UNSAFE. And you will need to set the database setting of `TRUSTWORTHY` to `ON`. 3. Your project having a target framework of .NET 4.0 should mainly show up with an error of "compiled for a newer version of .NET". But yes, SQL Server 2005, 2008, and 2008 R2 are bound to the CLR 2.0 series (i.e. Framework versions 2.0, 3.0, and 3.5) while 2012 and 2014 are bound to the CLR 4.0 series (i.e. Framework versions 4.0, 4.5, and so on). For more information on navigating these types of issues in SQLCLR projects, please see the [Stairway to SQLCLR][1] series that I am writing on SQL Server Central. Question 1 is addressed in Level 4 of the series, and Questions 2 and 3 are addressed in Level 5. [1]:
http://www.sqlservercentral.com/stairway/105855/
1 Person is following this question.
