[ACCEPTED]-Creating custom assembly in SQL Server throwing assembly not found in database error-clr

Accepted answer

Score: 11

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

I have added for 6 example System.Messaging.dll to SQL Server 5 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 4 other referenced assemblies linked to that 3 one. After that, I added Newtonsoft.Json.dll 2 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 1 Permission level as UNSAFE in Project Configuration.

Score: 6

Assemblies in SQL Server cannot reference 6 assemblies from the GAC unless they are 5 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 4 other assemblies need to be created within 3 SQL Server using the CREATE ASSEMBLY syntax - even those 2 from the .NET Framework itself that are 1 not on the list above.

Score: 3

I know this question has been asked long 24 back but I would like to share my findings 23 and solutions to it so that it could help 22 someone.

In order to create assembly be it 21 custom or native assembly from .NET Framework, you 20 need set TRUSTWORTHY ON for the database.

ALTER DATABASE [DATABASE NAME] SET TRUSTWORTHY ON;

After this, you 19 may execute the below script for creating 18 an assembly. In my example, I'm trying to 17 create assembly for "System.Net.Http"

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

For 16 your case, you need to point your customer 15 assembly (dll) location correctly to make 14 this work.

Note: There are only few supported 13 assembly which can be found here. If anything 12 other than specified assembly are used then 11 you will get an Warning as shown below. In 10 my example, when I tried to create assembly 9 for [System.Net.Http], I got below Warning.

The 8 Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you 7 are registering is not fully tested in 6 the SQL Server hosted environment and is 5 not supported. In the future, if you upgrade 4 or service this assembly or the .NET Framework, your 3 CLR integration routine may stop working. Please 2 refer SQL Server Books Online for more 1 details.

Hope this helps.

Source: stackoverflow.com

[ACCEPTED]-Creating custom assembly in SQL Server throwing assembly not found in database error-clr

Sql Server CLR load assembly failed

Where can I find location of generated file after doing Ngen?

C# Switch/case share the same scope?

Getting "Cannot find the type nvarchar(MAX)" when adding new CLR user defined function

Implementing C# for the JVM

C# StructLayout.Explicit Question

the common language runtime was unable to set the breakpoint

C# readonly vs Java final

Stack capacity in C#

What is the maximum number of parameters that a C# method can be defined as taking?