Select the product you need help with

Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

Article ID: 949080 - View products that this article applies to.

Symptoms

Consider the following scenarios.

Scenario 1

You create a common language runtime (CLR) routine that references a Microsoft .NET Framework assembly. The .NET Framework assembly is not documented in Knowledge Base article 922672. Then, you install the .NET Framework 3.5 or a .NET Framework 2.0-based hotfix.

Scenario 2

You create an assembly, and then you register the assembly in a Microsoft SQL Server database. Then, you install a different version of the assembly in the Global Assembly Cache (GAC).

When you execute the CLR routine or use the assembly from either of these scenarios in SQL Server, you receive an error message that resembles the following:

Server: Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'getsid':

System.IO.FileLoadException: Could not load file or assembly 'System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

System.IO.FileLoadException:

Back to the top | Give Feedback

Cause

When the CLR loads an assembly, the CLR verifies that the same assembly is in the GAC. If the same assembly is in the GAC, the CLR verifies that the Module Version IDs (MVIDs) of these assemblies match. If the MVIDs of these assemblies do not match, you receive the error message that the "Symptoms" section mentions.

When an assembly is recompiled, the MVID of the assembly changes. Therefore, if you update the .NET Framework, the .NET Framework assemblies have different MVIDs because those assemblies are recompiled. Additionally, if you update your own assembly, the assembly is recompiled. Therefore, the assembly also has a different MVID.

Back to the top | Give Feedback

Workaround

Workaround 1

To work around scenario 1 in the "Symptoms" section, you must manually update the .NET Framework assemblies in SQL Server. To do this, use the ALTER ASSEMBLY statement to point to the new version of the .NET Framework assembly in the following folder:

%Windir%\Microsoft.NET\Framework\Version

Note Version represents the version of the .NET Framework that you installed or updated.

Workaround 2

To work around scenario 2 in the "Symptoms" section, use the ALTER ASSEMBLY statement to update the assembly in the database.

If the problem still exists after you do this, drop the assembly from the database, and then register the new version of the assembly in the database.

Back to the top | Give Feedback

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

More information

We do not recommend that you use .NET Framework assemblies that are not documented in Knowledge Base article 922672. Knowledge Base article 922672 lists the assemblies that are tested in the SQL Server CLR-hosted environment.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

922672

(http://support.microsoft.com/kb/922672/ )

Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment

Description of CLR routines

CLR routines include the following objects that are implemented by using SQL Server integration with the .NET Framework CLR:

Scalar-valued user-defined functions (scalar UDFs)
Table-valued user-defined functions (TVFs)
User-defined procedures (UDPs)
User-defined triggers
User-defined data types
User-defined aggregates

Assemblies to update after you install the .NET Framework 3.5

After you install the .NET Framework 3.5, you must use the ALTER ASSEMBLY statement to update the following assemblies:

Accessibility.dll
AspNetMMCExt.dll
Cscompmgd.dll
IEExecRemote.dll
IEHost.dll
IIEHost.dll
Microsoft.Build.Conversion.dll
Microsoft.Build.Engine.dll
Microsoft.Build.Framework.dll
Microsoft.Build.Tasks.dll
Microsoft.Build.Utilities.dll
Microsoft.CompactFramework.Build.Tasks.dll
Microsoft.JScript.dll
Microsoft.VisualBasic.Vsa.dll
Microsoft.Vsa.dll
Microsoft.Vsa.Vb.CodeDOMProcessor.dll
Microsoft_VsaVb.dll
Sysglobl.dll
System.Configuration.Install.dll
System.Design.dll
System.DirectoryServices.dll
System.DirectoryServices.Protocols.dll
System.Drawing.dll
System.Drawing.Design.dll
System.EnterpriseServices.dll
System.Management.dll
System.Messaging.dll
System.Runtime.Serialization.Formatters.Soap.dll
System.ServiceProcess.dll
System.Web.dll
System.Web.Mobile.dll
System.Web.RegularExpressions.dll

These assemblies are in the following folder:

%Windir%\Microsoft.NET\Framework\v2.0.50727

How to preserve the data from user-defined data types after you drop an assembly

If you drop an assembly that a user-defined data type from SQL Server uses, you can use one of the following methods to preserve the data.

Assume that the following is the scenario:

You create an assembly whose name is MyAssembly.dll.
The MyAssembly assembly references the System.DirectoryServices.dll assembly.
You have a user-defined data type whose name is MyDateTime.
The MyDateTime data type uses the MyAssembly.dll assembly.
You create a table whose name is MyTable.
The MyTable table contains the data of the MyDateTime data type.

Method 1: Use the bcp.exe utility

Use the Bcp.exe utility together with the –n switch to copy the data from the MyTable table into a file. For example, run the following command at a command prompt:
bcp MyDatabase.dbo.MyTable out C:\MyFile.bcp -n –SSQLServerName -T
In SQL Server Management Studio, follow these steps:
1. Drop the MyTable table.
2. Drop the MyDateTime data type.
3. Drop the System.DirectoryServices.dll assembly.
4. Drop the MyAssembly assembly.
In SQL Server Management Studio, follow these steps:
1. Register the System.DirectoryServices.dll assembly.
2. Register the MyAssembly assembly.
3. Create the MyDateTime data type.
4. Create a new table that has the same table structure as the MyTable table.
Use the Bcp.exe utility together with the –n switch to import the data from the file into the MyTable table. For example, run the following command at a command prompt:
bcp MyDatabase.dbo.MyTable in C:\MyFile.bcp -n –SSQLServerName -T

Method 2: Use the INSERT … SELECT statement

Assume that the MyDateTime data type occupies 9 bytes in storage.

In SQL Server Management Studio, create a new table that contains a column of the VARBINARY(9) data type by running the following statement:
CREATE TABLE TempTable (c1 VARBINARY(9))
Run the following INSERT … SELECT statement to populate the TempTable table:
INSERT INTO TempTable SELECT CAST(c1 as VARBINARY(9)) FROM MyTable
In SQL Server Management Studio, follow these steps:
1. Drop the MyTable table.
2. Drop the MyDateTime data type.
3. Drop the System.DirectoryServices.dll assembly.
4. Drop the MyAssembly assembly.
In SQL Server Management Studio, follow these steps:
1. Register the System.DirectoryServices.dll assembly.
2. Register the MyAssembly assembly.
3. Create the MyDateTime data type.
4. Create a new table that has the same table structure as the MyTable table.
Run the following INSERT … SELECT statement to populate the MyTable table:
INSERT INTO MyTable SELECT c1 FROM TempTable