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.
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'
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.
(Sorry, didn't see this due to how it was tagged)
I would not recommend the approach taken in the accepted answer, as it should not be nearly that complicated.
SQL Server has it's own CLR host that is highly restricted. And it can only use one CLR version. So SQL Server 2005, 2008, and 2008 R2 are linked to CLR 2.0 and the .NET Framework versions linked to it (i.e. 2.0, 3.0, and 3.5), while SQL Server 2012 and newer are linked to CLR 4.0 and the .NET Framework versions linked to it (i.e. 4.0 and newer). So, since you are on 2012, I am not sure why it even mentioned 3.0 in the error message, but it isn't trying to use that one.
The error message also mentioned the 4.0 version, and that the problem was that the signatures did not match. One of the restrictions of the SQL Server CLR host is that any Assembly loaded into it, if also found in the GAC, must be the exact same version. The most likely cause of this issue is some combination of:
Somehow the server (i.e. the Windows OS) got a .NET Framework update and the GAC did not get refreshed. Not sure if you tried to reboot the server first before copying DLLs over, but that might have fixed it, or possibly using gacutil.exe to force the unload and reload of the current version of the DLL as found in C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319
Your development machine being at a different .NET Framework version than where the SQLCLR Assembly was being deployed to. It is best if these match. It also helps to make sure that in the .sqlproj file, in the section on "References", that the tag for the system DLLs do not have the "SpecificAttribute" property set.
This is a long-shot but has been an issue for at least two systems I helped with: the .NET Framework DLLs can be "corrupt", in which case you need to do a .NET Framework repair. You might be able to do this from Control Panel | Programs, or using Microsoft's .NET Framework Repair Tool.
