Add Entity Framework CLR DLL to SQL Server Database

Question

I have a program that is intensive in CRUD operations on a SQL Server Express 2012 database. I was hoping to create a CLR assembly that contained most of the code and then attach it to the database so that it would stay on the server. I was hoping to have resource-intensive calculations performed on each row in a specific table and then the results from this calculation will be entered into a column (not involved in the calculation itself) in that table. I wanted this to stay on the server so that the server could automatically do the calculations without the front-end application having to call it, and without the application having to submit TSQL back and forth from the server. I also wanted to write the code in VB.NET using Entity Framework to make things easier.

Before I wrote the real program, I decided to try this approach using AdventureWorks2012. I created a class library in Visual Studio, then built in in Release mode. I then went into SQL Server Management Studio, right-clicked the Databases > AdventureWorks2012 > Programmability > Assemblies node, and clicked "New Assembly...", I navigated to my built DLL and clicked OK, but SQL Server Management Studio gave me this error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for SqlAssembly 'AdventureWorks_01'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Assembly 'entityframework, version=6.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5058&EvtSrc=MSSQLServer&EvtID=6503&LinkId=20476

------------------------------

I noticed that this asked for an entityframework assembly, and there was one in the build folder, so I tried to add it using the same method, but I get this error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for SqlAssembly 'EntityFramework'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Assembly 'system.runtime.serialization, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.
Warning: The Microsoft .NET Framework assembly 'entityframework, version=6.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. (Microsoft SQL Server, Error: 6503)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5058&EvtSrc=MSSQLServer&EvtID=6503&LinkId=20476

------------------------------

I also saw a DLL named EntityFramework.SqlServer.dll in the folder, so I tried to add it, but it produced the first error message.

Here is my VB code:

Public Class Class1
    Sub AddNewProduct()
        Dim adWorks As New AdventureWorks2012Entities()
        Dim myProduct = New Product()

        With myProduct
            'add applicable product info here
        End With

        adWorks.Products.Add(myProduct)
    End Sub
End Class

Does anyone know how I can fix this problem, or if I can add Entity Framework CLR assemblies to an SQL Server database in the first place?

It seems an odd thing to do, adding those assemblies into Sql Server. Will your program run on the server? What is the reason for trying to put those assemblies into Sql Server? — Chris Dunaway, Oct 28 '14 at 13:36
What I am hoping to do is to have data residing on the server. From this data, resource-intensive calculations will be performed on each row in a specific table and then the results from this calculation will be entered into a column (not involved in the calculation itself) in that table. I was hoping that this would stay on the server so that the server could automatically do the calculations without the front-end application having to call it, and without the application having to submit TSQL back and forth from the server. — Milliron X, Oct 29 '14 at 21:06
Seems like it would be easier, and more appropriate, to write a little console app or windows service that lived on the server to do this. — Sam Axe, Mar 20 '15 at 04:52
Thanks for the advice, @Dan-o. I hadn't thought of that approach. I'll try it and see how it works. — Milliron X, Mar 21 '15 at 03:51

score 0 · Answer 1 · answered Dec 28 '14 at 04:08

You need to load all dependent assemblies in the chain as some assemblies are dependent on others. i.e. System.Runtime.Serialization needs t/b loaded as well. Also MSDN has an entire section on this topic hopefully these links will help:

(Supported .NET Framework Libraries)
http://msdn.microsoft.com/en-us/library/ms403279.aspx
For non-supported assemblies which kinda means assemblies not pre-loaded on SQL Server just add them via Create Assembly (see MSDN documentation on this) in SSMS or as you've been doing

(CLR Integration Programming Model Restrictions)
http://msdn.microsoft.com/en-us/library/ms403273.aspx

On MSDN you'll also see a section on Debugging SQL CLR code

Thanks for the input, @KVigor, but I am still having problems (specifically with the `Permission Set`). Could you give me a walkthrough please? — Milliron X, Jan 02 '15 at 17:11

Add Entity Framework CLR DLL to SQL Server Database

1 Answers1