DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 949080 - Last Review: January 7, 2013 - Revision: 3.0

On This Page

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:

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.

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.

Status

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

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

  1. 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
  2. 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.
  3. 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.
  4. 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.
  1. 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))
  2. Run the following INSERT … SELECT statement to populate the TempTable table:
    INSERT INTO TempTable SELECT CAST(c1 as VARBINARY(9)) FROM MyTable
  3. 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.
  4. 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.
  5. Run the following INSERT … SELECT statement to populate the MyTable table:
    INSERT INTO MyTable SELECT c1 FROM TempTable

References

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).aspx (http://msdn2.microsoft.com/en-us/library/51ket42z(VS.80).aspx)
For more information about how to update an assembly, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms186711.aspx (http://msdn2.microsoft.com/en-us/library/ms186711.aspx)
For more information about how to drop an assembly, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms177514.aspx (http://msdn2.microsoft.com/en-us/library/ms177514.aspx)
For 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.aspx (http://msdn2.microsoft.com/en-us/library/ms189524.aspx)
For more information about the Bcp.exe utility, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms162802.aspx (http://msdn2.microsoft.com/en-us/library/ms162802.aspx)

Applies to
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB949080
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support