c# - Sql Server CLR load assembly failed

Question

I am trying to deploy an CLR TVF (table value function). In the code I am using JavaScriptSerializer to parse some JSON string, so I reference the System.Web.Extensions dll and my problems start there.

Project builds fine, but when I try to register the dll I receive the following error:

Assembly 'my_assembly_name' references assembly 'system.web.extensions, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.', 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 referenced system.web.extensions from:

C:Program Files (x86)Reference AssembliesMicrosoftFramework.NETFrameworkv4.0System.Web.Extensions.dll

I can set copy load to true or manually copy the assembly but then referencing errors go deeper -

Assembly 'my_assembly_name' references assembly 'system.servicemodel.activation, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.', 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.

Everything works fine until I reference the Web.Extensions.dll. Target Framework is .NET 4.

Any ideas/solutions?

Answer 1

Acording to the documentation, any .NET assembly can be referenced but each dependant assembly must also be registered in the database (visible in the "Programmability - Assemblies" node in SQL Server Manager). Once the SQL Server Database has the dependant assemblies available they will be present in the Add Reference Dialog window in Visual Studio.

This SQL code below works. I had needed the System.Web.dll assembly (you need the correct version my 2005 SQL is 64 bit)

CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo 
FROM 'c:WINDOWSMicrosoft.NETFramework64v2.0.50727System.web.dll'
WITH PERMISSION_SET = UNSAFE 

The system will try to automatically register dependent assemblies but can only do it from the same directory as the file you are attempting to register. In my case it also registered the following assemblies in the database for System.Web to operate correctly...

System.Configuration.Install    
System.Design   
System.DirectoryServices    
System.DirectoryServices.Protocols  
System.Drawing  
System.Drawing.Design   
System.EnterpriseServices   
System.Runtime.Remoting 
System.Runtime.Serialization.Formatters.Soap    
System.ServiceProcess   
System.Web.RegularExpressions   
System.Windows.Forms

My system is using 3.5 Framework so I cannot test importing System.Web.Extensions.dll for you but it may require other assemblies from another folder to register, and since it is a 4.0 assembly it may be referencing some 2.0 or 3.5 assemblies that are in a different folder.

I suspect you will end up with a rather large list of assemblies imported to get it to work but it is definitly possible to do. The operation can probably be done more easily by having one folder with all the dependent dlls in it and register from there.