Managing CLR Assemblies in SQL Server

I've just employed a SQL Server CLR stored procedure in a project I'm working on (I was initially worried about performance, but having looked into it I've found that it can be quite good, provided you're mindful of marshalling boundaries etc.).

Having added a dedicated DLL project for SQL Server CLR artefacts (using .NET 2.0 as we target SQL Server 2005 in some instances) I extended our upgrade tool to generate and issue a CREATE/ALTER ASSEMBLY statement using a binary literal of this DLL. It was here I ran into one of Microsoft's weirder design decisions.

Running the following statement

ALTER ASSEMBLY SearchTools FROM 0xDEADBEEF...C001EE15

yields the following error if the new version of the assembly provided is identical to the existing one.

ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "SearchTools".

This is a bit of a pain as an update that worked once will fail on all subsequent runs. Why would they choose to do this? You wouldn't expect a standard DML UPDATE statement to fail if the new row data matched the existing ones, would you?

Anyway, I considered dropping the assembly if it exists and then issuing a CREATE ASSEMBLY statement each time but then there's the problem of the UDFs and Stored Procedures that depend on the installed assembly - forcing the eventual owners of this project to manually manage all these CREATE/DROPs wouldn't be very friendly so I had to find another way.

In the end I found the built in ASSEMBLYPROPERTY(N'asm-name', 'property-name') function that returns metadata from an installed assembly. The 'MvID' property can be used to extract the stored module version identifier for comparison with the one provided by the candidate version of the assembly:

// using a previously created SqlCommand
cmd.CommandText = "SELECT ASSEMBLYPROPERTY(N'SearchTools', 'MvID')";

var currentId = (Guid)cmd.ExecuteScalar();
var moduleId = typeof(SearchProcs).Assembly.ManifestModule.ModuleVersionId;

if (moduleId != currentId)
{
  log.WriteLine("Updating assembly ...");
  var raw = File.ReadAllBytes(typeof(SearchProcs).Assembly.Location);
  var hex = String.Join("", raw.Select(b => b.ToString("x2")).ToArray());
  cmd.CommandText = String.Format("ALTER ASSEMBLY SearchTools FROM 0x{0}", hex);
  cmd.ExecuteNonQuery();
}
else
{
  log.WriteLine("Assembly already up to date");
}

As a bonus the ASSEMBLYPROPERTY function will return NULL for the MvID property if the assembly named does not exist - this allows for CREATE/ALTER selection.

Cool.