SQLCLR Function and System.Runtime.Serialization In GAC

.net sql-server sql-server-2012 gac sqlclr
JIbber4568 picture JIbber4568 · Jan 13, 2016 · Viewed 7k times · Source

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

Abe picture Abe · Mar 20, 2017

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'

Related questions

how to insert datetime into the SQL Database table?

How can I insert datetime into the SQL Database table ? Is there a way to insert this query through the insert command in C# / .NET?

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and …

Read More