Interesting Exceptions: C# - Cannot load dynamically generated serialization assembly

June 19, 2011 by Christoff Truter C#   SQL  

If you ever tried calling a webservice (among other things) from a SQLCLR procedure chances are pretty good that you've ran into the following exception:

Msg 6522, Level 16, State 1, Procedure Test, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test": 
System.InvalidOperationException: 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.
System.IO.FileLoadException: 
   at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
   at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
   at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
   at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
   at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

For all intents and purposes I am going to assume you've got an understanding on how to write and deploy CLR procedures (You can learn more about SQLCLR procedures over here and here)

CLR Procedures that make use of webservices require a strongly typed wrapper class (a nice little performance optimization when working with XML serialization/deserialization, needed for SOAP requests/responses)

Notice the following setting (Generate serialization assembly) within your project properties page under the build tab (assuming you're using Visual Studio 2008)



When the serialization setting is set to auto/on and you're consuming a webservice in your project, a serialization assembly automatically gets generated in your output folder (e.g. SourceAssembly.XmlSerializers.dll) which you'll need to register in SQL like seen in the following snippet:

CREATE ASSEMBLY CLRProcedures FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.dll' 
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY CLRSerializer FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.XmlSerializers.dll' 
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE Test
AS EXTERNAL NAME [CLRProcedures].[CSTruter.com.StoredProcedures].[Test]

All of this works fine when we're consuming webservices, there is however another scenario where we'll get this exception when working with SQLCLR - which comes into play as soon as we use the XmlSerializer class.

When working with XML Types for example, the XmlSerializer class becomes quite useful in that instead of working with raw xml, we can rather work with a .net object like seen in the following snippet where we map our xml to a .net class.

[XmlRoot("tester")]
public class tester
{
    [XmlAttribute("name")]
    public string name
    {
        get;
        set;
    }
}

Serialization will look something like this:

tester t = new tester();
t.name = "test";
using (StringWriter sw = new StringWriter())
{
    XmlSerializer xs = new XmlSerializer(typeof(tester));
    xs.Serialize(sw, t);
}

While deserialization will look something like this:

tester t = null;
using (StringReader sr = new StringReader("some xml string"))
{
    XmlSerializer xs = new XmlSerializer(typeof(tester));
    t = xs.Deserialize(sr) as tester;
}

Like with webservices we'll also need to supply SQL with a strongly type wrapper class, but unfortunately visual studio only seems to automatically generate the serialization assembly when consuming webservices.

In order to manually generate a serialization assembly Microsoft created a tool called sgen, which is available in the .net sdk (and a few other microsoft SDKs), by which we can generate this serialization assembly.

Firstly you need to add the XmlSerializerAssembly attribute above the class(es) that you're using for serialization e.g.

[XmlRoot("tester")]
[XmlSerializerAssembly("SourceAssembly.XmlSerializers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")]
public class tester
{

Secondly run the sgen tool.

sgen C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.dll

(generates SourceAssembly.XmlSerializers.dll)

Simply register the generated assembly in SQL and that should get rid of this exception.