Article ID: 949080 - View products that this article applies to.
Consider the following scenarios.
Scenario 1You 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 2You 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=220.127.116.11, 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)
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.
Workaround 1To 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\VersionNote Version represents the version of the .NET Framework that you installed or updated.
Workaround 2To 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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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:
(https://support.microsoft.com/kb/922672/ )Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment
Description of CLR routinesCLR routines include the following objects that are implemented by using SQL Server integration with the .NET Framework CLR:
Assemblies to update after you install the .NET Framework 3.5After you install the .NET Framework 3.5, you must use the ALTER ASSEMBLY statement to update the following assemblies:
How to preserve the data from user-defined data types after you drop an assemblyIf 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:
Method 1: Use the bcp.exe utility
Method 2: Use the INSERT … SELECT statementAssume that the MyDateTime data type occupies 9 bytes in storage.
For more information about the assembly version, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/51ket42z(VS.80).aspxFor more information about how to update an assembly, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms186711.aspxFor more information about how to drop an assembly, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms177514.aspxFor more information about how to register an assembly in a SQL Server 2005 database, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms189524.aspxFor more information about the Bcp.exe utility, visit the following MSDN Web site:
Article ID: 949080 - Last Review: January 7, 2013 - Revision: 3.0