2

I am trying to create an assembly from a specific dll. The command which I execute is

CREATE ASSEMBLY "xx.xx.blobviewer" from 'D:\xx\xx\xx\BlobSniffer\BlobSniffer.dll' WITH PERMISSION_SET = unsafe

The message I get for the response is:

Assembly 'BlobSniffer' references assembly 'system.runtime.serialization, 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.

I tried to copy requested .net dll-s to the BlobSniffer folder, but after i copy all the referenced assemblies i get this:

CREATE ASSEMBLY for assembly 'BlobSniffer' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub.

First question, why did I even need to copy .net assemblies, weren't they supposed to be retrieved automatically?

Second question, what is wrong with the assembly being flagged as malformed? The assembly is also part of the .net framework and there are no external classes.

Improve this question
1
  • 1
    omegasbk: Not sure if you had noticed, but @rolo posted an answer that is pretty much correct. I added a little detail in a comment on that answer just to make sure that your first question was answered directly. But that pretty much is the answer. For more info on working with SQLCLR, I am writing a series called Stairway to SQLCLR on SQL Server Central (free registration required). Level 5 "Using .NET within SQL Server" deals with this issue specifically. Commented Aug 17, 2015 at 4:25

1 Answer 1

Reset to default
5

Take a look here:

http://msdn.microsoft.com/en-us/library/ms189524.aspx

The set of system assemblies it references is one of the following supported assemblies in SQL Server:

  • Microsoft.Visualbasic.dll
  • Mscorlib.dll
  • System.Data.dll
  • System.dll
  • System.Xml.dll
  • Microsoft.Visualc.dll
  • Custommarshallers.dll
  • System.Security.dll
  • System.Web.Services.dll
  • System.Data.SqlXml.dll
  • System.Core.dll
  • System.Xml.Linq.dll

Other system assemblies can be referenced, but they must be explicitly registered in the database.

http://blogs.msdn.com/b/psssql/archive/2013/02/23/unable-to-register-net-framework-assembly-not-in-the-supported-list.aspx

Regardless which version of SQL Server, CREATE ASSEMBLY only allows pure .NET assemblies to be registered. SQL Server has always required that an assembly to be loaded into SQL Server database with CREATE ASSEMBLY contains only MSIL instructions (pure assembly). CREATE ASSEMBLY will raise the above error if an assembly to be registered is mixed assembly.

I hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is pretty much spot-on. +1. The only thing I would add, to answer the original question more completely, is that the reason "why" the DLL(s) had to be copied is that SQL Server's CLR host is highly restricted and doesn't automatically load any assemblies, hence it doesn't need a search path to look for libraries. Assemblies need to be manually registered, and SQL Server will only look for related assemblies in the same folder.
The blog post is no longer visible. Is an alternative source for the solution available?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.