1

This is my first time working with SQL Server 2012 and I'm having a problem getting access to the database. I did a backup and restore from a SQL Server 2008 R2 database, set the logins and permissions, set the database to trustworthy and enabled clr.

I'm using a web application (IIS, ASP.Net) to connect to the database. When I point the web server to the new database I get the following error. When I point it to a copy of the database on a SQL Server 2008 R2 machine, it connects.

I’m running the application as 2.0/3.5 version and tried to make sure that everything is pointed to that framework. I've verified that the dll is in the same folders on both database servers.

Error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "clrSP_UserData_PermList":
System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

System.IO.FileNotFoundException:

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializerContract.CanSerialize(Type type)
at System.Xml.Serialization.TempAssembly.LoadGeneratedAssembly(Type type, String defaultNamespace, XmlSerializerImplementation& contract)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
at Testdb.SqlServer.SqlUtility.XmlSerialize(Object oSerializableObject)
at Testdb.SqlServer.StoredProcedures.clrSP_UserData_ PermList (Guid gUserID, SqlChars& sqlCharList)

I’ve searched the internet for days and I can’t find a solution that works for this database (clr is enabled, correct framework is installed, etc..).

abatishchev
  • 98,240
  • 88
  • 296
  • 433
CL95
  • 11
  • 1
  • 2
  • See [this question](http://stackoverflow.com/questions/255669/how-to-enable-assembly-bind-failure-logging-fusion-in-net). – Jester Mar 20 '17 at 20:47
  • Not sure - you're talking about SQL CLR - you need to be aware that SQL Server 2005 - 2008 R2 used a .NET 2.0 based SQL CLR, while as of SQL Server **2012**, the SQL CLR is based on .NET **4.0** ..... – marc_s Mar 20 '17 at 21:38

1 Answers1

0

Given that System.Data.DataSetExtensions is not in the list of Supported .NET Framework Libraries, I'm going to assume that you manually loaded it into SQL Server via CREATE ASSEMBLY (and hence why you had to enable TRUSTWORTHY). Since you restored the Database, all of the Assemblies in it came along for the ride (which is a good thing, most of the time). BUT, in this particular case it is not so helpful. The issue is that SQL Server's CLR host is highly restricted and is linked to a specific CLR version. SQL Server 2005, 2008, and 2008 R2 are linked to CLR 2.0 and its related .NET Framework versions (2.0, 3.0, and 3.5), while SQL Server 2012 and newer is linked to only CLR 4.0 and its related .NET Framework versions (4.0 and newer). Because you started on 2008 R2, you manually loaded the .NET 3.5 version of System.Data.DataSetExtensions. But now that the Database is running within SQL Server 2012, CLR 4.0 isn't going to load a library compiled for CLR 2.0 / Framework version 3.5. You will need to replace the existing System.Data.DataSetExtensions in your database with the 4.0 version.

  1. I don't think this will work due to System.Data.DataSetExtensions being a dependency linked to your Assembly, but you could try dropping it and re-adding it.

  2. If dropping and recreating doesn't work, you might could try doing an ALTER ASSEMBLY on it, which is an in-place replacement of the current definition. There are restrictions on when you can do an ALTER ASSEMBLY, but I think the fact that it doesn't have any SQLCLR exposed methods might allow this to actually work.

  3. If those first two ideas don't work, then you will need to drop your Assembly, then System.Data.DataSetExtensions, and then recreate System.Data.DataSetExtensions with the 4.0 version, and then re-deploy your Assembly.

After you manage that, cross your fingers, pray, make offerings, rub a lucky rabbit's foot, and hopefully a) System.Data.DataSetExtensions did not get changed into a mixed-mode Assembly (at any point in time, from the initial release of 4.0 up through the current 4.6.2 or whatever the most recent Framework version is), and b) you are not also using System.ServiceModel since it did change from pure MSIL into mixed-mode in that 4.0 transition, and thus became unloadable starting in SQL Server 2012 given that one of those restrictions in SQL Server's CLR host is that only pure-MSIL assemblies are allowed.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171