Load DLL Assembly in SQL Server

Question

Hello,

I build a DLL assembly which refer to Excel interop assembly in GAC.

When I use “create assembly “ in SQL server (2008) to load my DLL,

I got error 

“Msg 6501, Level 16, State 1, Line 1

CREATE ASSEMBLY failed because it could not open the physical file "microsoft.office.interop.excel, version=14.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.": 3(The system cannot find the path specified.).”

Is there a way to let SQL server be able to search GAC for system provided Excel interop assembly my DLL depend upon? Or any way we can get this problem resolved?

I built my DLL in C# containing code below:

(after add reference to Excel COM)

using Microsoft.Office.Interop.Excel;

Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp = new ApplicationClass();

Thanks.

Answer 1

CREATE ASSEMBLY only looks for dependent assemblies in one place; the directory in which the assembly lives. It does not look in the GAC. So, you have two choices:

1. Manually deploy the dependent assemblies first. Either from the file system, or read them into a bitstream, check the CREATE ASSEMBLY syntax for details. Then, deploy the assembly that uses them.

2. Place all the dependent assemblies in the same directory as the “main” assembly (i.e your assembly). Then run CREATE ASSEMBLY from your assembly.

 

Unless you’re an MSBUILD wizard, I’d suggest manual deployment over VS auto-deploy in this situation.

 

Also (although I’m pretty sure you already know), bear in mind that the Excel Interop assemblies have not been tested and are not supported in SQLCLR. So, both the Excel Interop assemblies and your assembly must be CREATEd with permission_set = unsafe. And, that you don’t have any support if it breaks (either because of new versions/SPs of Excel or of the .NET framework). See the blog entry http://www.sqlskills.com/BLOGS/BOBB/post/Installing-NET-35-on-a-SQL-Server-2005-machinebe-careful.aspx for one of the versioning issues that may arise with unsafe in future.

 

I’m not saying “you can’t do it”; I’m saying its unsupported. Keep your wits about you and understand exactly how this (using unsupported assemblies) works.

 

Cheers,

Bob