How to add assembly in GAC to SQL 2005?

I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?

thanks!

You can copy the .dll file out of the GAC and try to register it. I have not tried this assembly, but I would expect that there may be some issues using it within SQL Server. You could give it a shot, of course.

Cheers,
-Isaac|||

Hi,

I am also trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?

Normally interop assemblies (as well as system assemblies) located in the gac also has a location outside the GAC, but I can not find the Excel.dll. If it had a location outside you would then run CREATE ASSEMBLY against it in that location.

As I can't find it you have to do it as Isaac says. Use command line and cd to where the physical location of that assembly in the GAC: windows\assembly\gac\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c

copy it from there to somewhere on your hard-drive and run CREATE ASSEMBLY against it. However, this assembly has dependencies on other assemblies (use ILDASM to view the dependencies) . So you need to copy those as well out of the GAC, unless they are assemblies that SQL Server loads from the GAC (such as mscorlib, System.Data etc) and put them in the same location as your original assembly. Notice that these dependency assemblies can have dependencies on other assemblies too.

Finally, you have to create the assembly with permission set UNSAFE.

Niels