How to reference GAC assemblies when integrating a CLR extension into SQL Server
I've created an assembly for CLR integration in SQL Server 2008. It has one reference to System.Web.Extensions, which is an issue because when I try to add my assembly, I get the following error:
Assembly 'system.web.extensions, version=3.5.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)
How do I get SQL Server to reference the required assembly?
Answer
Apparently I can't, according to this post on the microsoft forums:
CLR integration in SQL Server 2005 supports only a subset of .NET framework libraries to be references and used inside SQL Server. These are:
- CustomMarshalers
- Microsoft.VisualBasic
- Microsoft.VisualC
- mscorlib
- System
- System.Configuration
- System.Data
- System.Data.OracleClient
- System.Data.SqlXml
- System.Deployment
- System.Security
- System.Transactions
- System.Web.Services
- System.Xml
These libraries have been tested to ensure they are reliable to run inside SQL Server. These libraries can be referenced in any code and do not have to be registered using CREATE ASSEMBLY. These are the only assemblies that SQL Server allows CLR to load from GAC. All other assemblies (within .NET framework or otherwise) need to be registered explicitly inside the database. Any code that is outside of these libraries should be tested well by the user for reliability and security.
While the assemblies you are trying to registered are not supported by CLR integration you can use them if you test your functionality well. An easy way to register system.web and all its dependencies is to register them from the .NET framework install directory (usually c:\windows\microsoft.net\framework\)
e.g:
CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
Since all the dependent assemblies are in the same directory, SQL Server would automatically register them.
Thanks,
-Vineet.