One of the most outstanding SQL Server 2005 feature is CLR stored procedures. A CLR stored procedure can encapsulate complex .NET code, process data, and pipe the results back to your report. The advantage of using CRL stored procedures is that from a reporting perspective things are very simple. The report just needs to call the CLR stored procedure, as it would call a regular SQL Server stored procedure.

One of my reporting requirements called for passing some XML metadata in the form of a serialized-to-XML object. The stored procedure had to deserialize the object before it can use it. In my case, the object used a custom XML serialization/deserialization by implementing IXmlSerializable interface. My first attempt to deserialize the object from the CLR stored procedure failed abysmally with the following exception:

Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

Initially, I couldn't understand what was going on since I've given my assembly UNSAFE rights, which means that it should be able to do anything. Luckily, googling on this exception brought me to the Vineet’s post which hinted that for security and reliability reasons, SQL Server does not support loading assemblies dynamically. After some experimenting, I was able to fix the issue by following these steps:

1. Sgen the object assembly to produce a pre-generated XmlSerializers assembly.
2. Deploy both the object and XmlSerializers assembly to SQL Server with UNSAFE access. Strangely, I had to deploy also System.Drawing .NET assembly because SQL Server was asking for it.
3. In my CLR database project in VS.NET, add a reference to both the object assembly and the XmlSerializers assembly.
4. Inside the CLR stored procedure, instead of deserializing the object directly, I had to use the XML serializer from the XmlSerializers assembly.

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void csp_MyStoredProcedure(SqlXml metadata)
    {
        // Deserialize the object using the XmlSerializers serializer
        FooSerializer serializer = new FooSerializer ();
        Foo f = (Foo)serializer.Deserialize(metadata.CreateReader());
        . . .
    }

So, you need to go through a few development, deployment, and management hoops just to deserialize an object in a CLR stored procedure. Not sure if security is a good justification for this effort though. Moreover, reflecting on the XmlSerializers assembly revealed that it is just a thin wrapper on top of the object assembly. One would say that you never have too much secuirty although, in this case, it certainly feels that way. Are we getting paranoid with security?