Creating custom assembly in SQL Server throwing assembly not found in database error

Question

I am trying to create an assembly for a third party dll (developed in .net) in SQL Server 2008 for CLR procedure project,

create assembly [`XXXX.XXX.XXX.dll`]  
authorization dbo  
from 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\XXXX.XXX.XXX.dll'  
with permission_set = unsafe

But I'm getting this error while executing the above script:

Assembly 'XXX.XXX.XXX.XXX' references assembly 'XXX.XXX.XXXXX', version=0.0.0.0, culture=neutral, publickeytoken=null.', 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 appreciate your help in solving this issue

Answer 1

You can add Assemblies to your database which is used by you CLR.

I have added for example System.Messaging.dll to SQL Server 2014 as follows

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE;
GO 

It will add the neccessary other referenced assemblies linked to that one. After that, I added Newtonsoft.Json.dll like this:

CREATE ASSEMBLY [Newtonsoft.Json]
FROM 'C:\Users\RiaanDL\Downloads\Json60r3\Bin\Net45\Newtonsoft.Json.dll'
WITH PERMISSION_SET = UNSAFE;
GO

Just remember to set your SQLCLR Permission level as UNSAFE in Project Configuration.

Answer 2

Assemblies in SQL Server cannot reference assemblies from the GAC unless they are on the 'blessed' list. That list is as follows:

• Mscorlib.dll
• System.Data.dll
• System.dll
• System.Xml.dll
• System.Security.dll
• System.Web.Services.dll
• System.Data.SqlXml.dll
• System.Transactions.dll
• System.Configuration.dll
• Microsoft.VisualBasic.dll
• Microsoft.VisualC.dll
• CustomMarshalers.dll
• System.Data.OracleClient.dll

Any other assemblies need to be created within SQL Server using the CREATE ASSEMBLY syntax - even those from the .NET Framework itself that are not on the list above.