SQLCLR Function and System.Runtime.Serialization In GAC

Question

I've built a SQLCLR function in C# that will deserialize JSON and return a table.

The problem I have is getting the correct assemblies within SQL Server 2012.

In order to utilize Newtonsoft's deserializer I've had to add the following assemblies to SQL Server:

System.ServiceModel.Internals.dll
SMDiagnostics.dll
System.Runtime.Serialization.dll
Newtonsoft.Json.dll

This has all gone as planned but when I try to run my function I get the following error:

System.IO.FileLoadException: Could not load file or assembly 'System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information. ---> System.IO.FileLoadException: Could not load file or assembly 'System.Runtime.Serialization, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

The server has .NET 4 installed and the DLL I have tried to add is from that. However the DLL shown within C:\Windows\assembly is version 3.0.0.

I used Powershell to update the DLL in the GAC, but that only updates the DLL located within C:\Windows\Microsoft.NET\assembly\GAC_MSIL.

How on earth do I get the GAC at C:\Windows\assembly (which I assume is the one SQL Server is comparing it to) to reflect the correct assembly?

Any help is greatly appreciated.

Answer 1

Run this script in SQL and this should resolve the issue

ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'

Answer 2

Finally managed to get this working. Not sure how helpful this answer will be to others but I had to use a bit of a mix of dll's from various sources.

Basically I took all of the dll's from my local machine and copied them to the server and then tried combinations of references to the local files and files within the .Net framework.

In the end I had to reference 1 of the dll's from .Net on the server and the rest of them copied from my local machine.

This got it working and also then worked when moved to production server. Not the simplest of solutions but all I can advise anyone in a similar situation is just to try playing around with various combinations of references.