Using .Net 3.5 assemblies SQL 2005 CLR?

Question

I have a CLR stored procedure that references an assembly created in VS 2008 that uses Linq. Lets call this assembly 'MyLib'.

I can't seem to get 'MyLib' into my SQL 2005 database. I do the following:

CREATE ASSEMBLY [MyLib]
    FROM 'C:\MyLib\bin\Release\MyLib.dll'
WITH PERMISSION_SET = UNSAFE
GO

But I get the error:

Assembly 'MyLib' references assembly 'system.core, version=3.5.0.0, 
culture=neutral, publickeytoken=b77a5c561934e089.', which is not present 
in the current database. SQL Server attempted to locate and automatically 
load the referenced assembly from the same location where referring assembly 
came from, but that operation has failed (reason: 2(error not found)). Please
load the referenced assembly into the current database and retry your request.

Is there an easier way to get all of the .Net 3.5 assemblies into the SQL 2005 CLR, other than me writing out a 'CREATE ASSEMBLY' command for each one? Is there some "best practice" way of doing this?

Answer 1

SQL Server 2005 shipped with the v2.0.50727 CLR - which did NOT include LINQ just yet.

You won't be able to use LINQ in SQL 2005 CLR assemblies - you need to stick to the original set of .NET 2.0 assemblies.

The only slim chance there is would be to include all the relevant 3.5 system assemblies (i.e. System.Linq, System.Data.Linq) in your deployment - they're all based on the .NET 2.0 CLR so that might be possible - I personally probably wouldn't bother trying, seems like too much work and too much hassle.

SQL Server 2008 R2 will most likely ship with a new CLR included (just my guess - no official confirmation or announcement available just yet, AFAIK).

Marc

Answer 2

Check out this thread. Basically you have to manually get the new assemblies to load, instead of them being loaded automatically from the global assembly cache.

(the CLR for version 2.0 and 3.5 is the same)