7/11 Windows update broke my stored procedure

I have a managed code DLL that is used as a CLR assembly in my database. A stored procedure in the database references this CLR assembly.
My CLR assembly has a dependency on Microsoft's System.Management.dll.
In order to get my assembly to work, I added System.Management.dll as an assembly to the database using:

CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

For months this stored procedure has worked fine... up until today.

Today (7/11/07) I noticed a new Microsoft update was available.
I applied the patch to an XP machine that has my database and now my CLR assembly stored procedure no longer works.
I checked a 2003 Server machine that did not have the latest patch and see that the stored procedure is working fine.
I then apply Microsoft update to the 2003 server machine.
Now the stored procedure no longer works on the 2003 machine.

I deleted my stored procedure and CLR assembly and then added them back with no success.
I then deleted and added back the System.Management.dll assembly again and suddenly now my stored procedure works again.

This is BAD. I can't have Windows updates blowing up my app.
What am I doing wrong here?
I must use the System.Management.dll in my CLR stored procedure but I can't have changes to Microsoft's files causing me to be unable to reference them as assemblies.

How can my CLR stored procedure assembly reference a Microsoft assembly such that changes to the Microsoft assembly does not cause version mismatches?

Hmm, that's weird. In what way does you proc no longer work?

The reason I think it is weird is that as the dll in question is loaded from the database, having a new dll should not really matter, unless the patch also changes something else that the dll calls into and therefore fails.

So, once again - in what way does your proc fail?

Niels

|||

Unfortunately I do not remember the exact phraseology of the exception (the problem has been fixed on my development machine) but it did mention that the assembly it was looking for did not match the version found in the GAC. I'm certain it mentioned the GAC.

I will include as much detail as I can below.

I have a web app that makes calls to the stored procedure like this:

Code Snippet

try
{
using (SqlConnection sqlConnection = new SqlConnection(strConnectionString))
{
sqlCommand = new SqlCommand("usp_MyCLRStoredProcedure", sqlConnection);

sqlConnection.Open();

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlParameter = new SqlParameter("@.iRetVal", SqlDbType.Int);
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@.iVersion", SqlDbType.Int);
sqlParameter.Value = (int)eVersion;
sqlCommand.Parameters.Add(sqlParameter);

// After applying the 7/11 Windows update, execution of
// the next line results in an exception being thrown
sqlCommand.ExecuteNonQuery();

// Code after this point never executes due to exception being thrown...

}
}
catch (Exception e)
{
Console.WriteLine("Error while attempting to access database:\n" + e.ToString());
}

The CLR stored procedure is a SQL Server Project written in Visual Studio and compiled into a DLL. This C# code fetches the CPU ID of the machine, and does some cryptography work. My CLR stored procedure is similar to the following code block (I've tried to omit non-relevant parts):

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Management;
using System.IO;
using System.Security.Cryptography;

public partial class StoredProcedures
{

// This is the entry point of the stored procedure
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyStoredProcedure(int iVersion)
{
// ... code ...

// calls are made to Environment.MachineName

// ... more code ...

// get the processor id
ManagementClass managementClass = new ManagementClass( "Win32_Processor" );
ManagementObjectCollection mocInstances = managementClass.GetInstances();

// ... more code ...
// Calls are made to System.Security.Cryptography.HMACSHA1
// Calls are made to System.Text.Encoding
// Calls are made to System.Convert
// ... more code ...

// Call ExecuteNonQuery() on a regular (T-SQL based) stored procedure

// ... more code ...
}
}

To hook the CLR DLL code above with a stored procedure that is accessible by SQL Server, I performed the following from within Microsoft SQL Server Management Studio:

Code Snippet

USE MyDatabase
GO

-- SQL server needs to know if a database is "trustworthy" before it
-- will allow it to load any extra assemblies
ALTER DATABASE MyDatabase SET trustworthy ON
GO

-- To add a reference to a "SQL Server Project" within Visual Studio
-- you must first add the dll you want to reference to the database itself
-- The following code block places "System.Management.dll" and all of
-- the assemblies it depends on in the database.
-- After completing this step, you should be able to use the "Add Reference"
-- feature of Visual Studio to add the "SystemManagement" class as a
-- reference in your SQL Server Project.
CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The "compatibility level" of the Database needs to be set to 90
-- before it has the ability to recognize CLR calls
exec sp_dbcmptlevel 'MyDatabase', 90
GO

-- SQL Server 2005 won't recognize CLR calls unless you manually enable it.
-- To enable the CLR on SQL Server 2005, execute the code block below
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Load a CLR DLL as an assembly onto the database
CREATE ASSEMBLY MyAssembly
FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The following code block demonstrates how to create and associate
-- a stored procedure with a method in a CLR assembly.
-- Of course, the CLR assembly must already be registered
-- with the database (as shown above) before this is attempted.
-- The "EXTERNAL NAME" clause takes the following parameter
-- [Assembly name (as shown in MSSQL Server Management Studio)].ClassName.MethodName
CREATE PROCEDURE usp_MyCLRStoredProcedure
@.iVersion int
AS EXTERNAL NAME MyAssembly.StoredProcedures.MyStoredProcedure
GO

-- Give execute priveleges to the newly added stored procedure
GRANT EXECUTE ON usp_MyCLRStoredProcedure TO MyWebAppUser
GO

-- To debug the CLR stored procedure, the PDB file
-- must be added to the assembly.
-- 1) The MyCLRStoredProcedure.PDB file must
-- be installed in the database; execute the
-- T-SQL code block below.
-- 2) In the Visual Studio IDE, select
-- Debug -> Attach to Process
-- 3) Select "sqlserver.exe" from the Available Processes
-- list box and click the "Attach" button.
-- 4) Breakpoints should now be functional in
-- the MyCLRStoredProcedure source code.
ALTER ASSEMBLY MyAssembly
ADD FILE FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.pdb'
GO

At this point, everything is wired up and ready to go.

Prior to the 7/11 Windows update, a user of the web app could initiate a call to "usp_MyCLRStoredProcedure" and everything would work fine.
After applying the 7/11 Windows update, an exception now occurs somewhere during the call to usp_MyCLRStoredProcedure.

After deleting the stored procedure "usp_MyCLRStoredProcedure" and the assemblies "MyAssembly" and "SystemManagement" from the database and then re-running the SQL code block shown above, everything worked normally again.

I have one machine that I haven't fixed yet but it does not have any sort of development environment. I can try to get a development environment set up on the machine or instrument the web app to log the exception text but in the meantime if anyone has any clues or sees something I am doing wrong in the code above, please let me know.

Thank you.

|||it would be definitely easier to help you with the exact error message that is thrown.

Jens K. Suessmeyer.

http://www.sqlserver2005.de