CODEWITHSUNDEEP
sql-serversqlclr
joebohen
joebohen

Reputation: 143

Building SQL CLR project in Visual Studio 2015

I am attempting to build a SQL CLR project in Visual Studio 2015 and I am calling a wcf service which targets the System.Runtime.Serialization.dll. I am having problems identifying the correct version of .NET to target in order to overcome this.

The sql version is:

name        value
directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version     v4.0.30319
state       CLR is initialized

I attempted to build the CLR against .NET version 4.

SQL refuses to install System.Runtime.Serialization.dll If I build against .NET version 3.5, I get

Msg 6586, Level 16, State 1, Line 8 Assembly 'System.Runtime.Serialization' could not be installed because existing policy would keep it from being used.

What version should I build against to overcome this?

Upvotes: 1

Views: 737

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48816

SQL Server 2012 and newer uses CLR 4.0, which in turn is tied to .NET Framework versions 4.0 and above. You cannot use any .NET 2.0, 3.0, or 3.5 libraries in SQL Server 2012 or newer.

System.Runtime.Serialization is not in the Supported .NET Framework Libraries list, so you need to load it manually, and as UNSAFE. However, if that DLL is mixed mode and not pure MSIL, then it won't load since SQL Server's CLR host only works with pure MSIL libraries. I have found it best to just use HttpWebRequest and build the request XML manually and parse the response XML manually.

In the past, the ServiceModel DLL, and all of its dependencies, were pure MSIL and worked in SQL Server 2008 R2 (SQL Server 2005, 2008, and 2008 R2 are tied to CLR 2.0). But then, in CLR version 4.0, Microsoft added some dependencies to ServiceModel. One of them was the new Microsoft.VisualBasic.Activities.Compiler library (not a direct dependency, but via another, most likely Microsoft.Transactions.Bridge), which happens to contain unmanaged code (which cannot be loaded into SQL Server). Hence, everyone who had working WebService SQLCLR stuff running in 2008 R2 had it stop working upon upgrading to SQL Server 2012 or newer. Using HttpWebRequest works with an "approved" library that is guaranteed to always works across upgrades of the .NET Framework.

There are numerous questions already on here regarding these issues. I will try to find some and update this with a few of them.

Also, just FYI, the output shown in the question, which appears to be the results of executing: SELECT * FROM sys.dm_clr_properties;. Please be aware that the "version" shown, while often referred to as the Framework version, is actually just the CLR version, and those two things are not the same thing.

Upvotes: 2

Related Questions