Error message when you use a common language runtime object in SQL Server 2005: "Cannot load dynamically generated serialization assembly"

Bug #: 101935 (SQLBUDT)


When you use a common language runtime (CLR) object in Microsoft SQL Server 2005, you may receive an error message that is similar to the following:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'ObjectName':
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.

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)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] s
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSe...
For example, you may receive the error message when you use a CLR object that calls a Web service or performs conversion from user-defined types to XML inside SQL Server.


This issue occurs when a CLR object is converted to the XML data type. When this conversion occurs, the Windows Communication Foundation (formerly code-named "Indigo") tries to do the following:
  • Generate a new XML serialization assembly.
  • Save the assembly to disk.
  • Load the assembly into the current application domain.
However, SQL Server does not allow for this kind of disk access in the SQL CLR for security reasons. Therefore, you receive the error message that is mentioned in the "Symptoms" section. Several scenarios may cause the CLR object to be converted to the XML data type.

For more information about the Windows Communication Foundation, visit the following Microsoft Developer Network (MSDN) Web site:You may receive the error message that is mentioned in the "Symptoms" section in the following scenarios:
  • The CLR code that implements CLR objects explicitly uses the XmlSerializer class. These CLR objects may include stored procedures, functions, user-defined types, aggregates, and triggers.
  • You use a Web service in the CLR code.
  • You send or receive CLR objects to or from SQL Server by using direct HTTP/SOAP access to SQL Server.
  • The CLR object converts a user-defined type to the XML data type.


To resolve this issue, you must use the XML Serializer Generator tool (Sgen.exe) to create the XML serialization assembly for the original assembly manually. Then, load the assemblies into a SQL Server database.

Code example

For example, you may want to create a CLR function that returns XML data by using an assembly that is created by the following code example:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Serialization;
using System.Text;
using System.IO;
public partial class StoredProcedures
public static SqlString XMLTest()
Person p = new Person();
return new SqlString(p.GetXml());

public class Person
public String m_FirstName = "Jane";
public String m_LastName = "Dow";

public String GetXml()
XmlSerializer ser = new XmlSerializer(typeof(Person));
StringBuilder sb = new StringBuilder();
StringWriter wr = new StringWriter(sb);
ser.Serialize(wr, this);

return sb.ToString();

When you call the XMLTest function in SQL Server Management Studio, you expect to receive the following result:
<?xml version="1.0" encoding="utf-16"?>
<Person xmlns:xsi="" xmlns:xsd="">
To return the correct result, you must manually create the XML serialization assembly for the original assembly. Use one of the following methods to create the serialization assembly manually.

Note These methods assume that the following conditions are true:
  • You have created a dbTest database in an instance of SQL Server 2005.
  • All the project files are saved in the C:\CLRTest folder.

Method 1: Build a SQL Server CLR project by using Microsoft Visual Studio 2005

You can create the serialization assembly by using the Build Events option in Microsoft Visual Studio 2005. To do this, follow these steps:
  1. Start Visual Studio 2005.
  2. Create a new SQL Server project that is named MyTest.
  3. In the Add Database Reference dialog box, click the reference that connects to the dbTest database, and then click OK.

    If the reference is not in the list, you must create a new reference. To do this, click Add New Reference.
  4. On the Project menu, click Add User-Defined Function. The Add New Item dialog box appears.
  5. Click Add to add a new file. By default, the file is named Function1.cs.

    Note You receive the error message that is mentioned in the "Symptoms" section if you deploy the project to the database and then run the following Transact-SQL statement:
    SELECT [dbTest].[dbo].[XMLTest] ()
    You must follow steps 6-16 to resolve this issue.
  6. Add the code that is listed in the "Code example" section to the Function1.cs file.
  7. On the Project menu, click MyTest Properties.
  8. On the MyTest dialog box, click the
    Build Events option.
  9. Type the following command in the Post-build event command line box:
    "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"
    Note The /force option generates a new serialization assembly every time that you modify the source assembly. Additionally, you must modify this command if you installed Visual Studio 2005 in another folder.
  10. In the C:\CLRTest folder, create two text files that are named Predeployscript.sql and Postdeployscript.sql.
  11. Add the following Transact-SQL statements to the Predeployscript.sql file:
    IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyTest.XmlSerializers') 
    DROP ASSEMBLY [MyTest.XmlSerializers]
  12. Add the following Transact-SQL statements to the Postdeployscript.sql file:
    CREATE ASSEMBLY [MyTest.XmlSerializers] from
    WITH permission_set = SAFE
  13. On the Project menu, click Add Existing Item.
  14. In the Add Existing Item dialog box, locate the C:\CLRTest folder, and then click All Files (*.*)in the Files of type list.
  15. In the File name box, type
    Postdeployscript.sql;Predeployscript.sql, and then click
  16. On the Build menu, click Deploy MyTest.
  17. Run the following Transact-SQL statement in SQL Server Management Studio:
    SELECT [dbTest].[dbo].[XMLTest] ()
    You receive the correct result.

Method 2: Build a SQL CLR project at the Visual Studio Command Prompt window

  1. Locate the C:\CLRTest folder.
  2. Create a text file that is named MyTest.cs.
  3. Add the code that is listed in the "Code example" section to the MyTest.cs file.
  4. Open the Visual Studio 2005 Command Prompt window.
  5. Type CD C:\CLRTest, and then press ENTER.
  6. Type csc /t:library MyTest.cs, and then press ENTER.
  7. Type sgen.exe /force MyTest.dll, and then press ENTER.
  8. Run the following Transact-SQL statements in SQL Server Management Studio:
    USE dbTest
    CREATE ASSEMBLY [MyTest] from 'C:\CLRTest\MyTest.dll'
    CREATE ASSEMBLY [MyTest.XmlSerializers.dll] from 'C:\CLRTest\MyTest.XmlSerializers.dll'

    RETURNS nvarchar (max)
    EXTERNAL NAME MyTest.StoredProcedures.XMLTest
  9. Run the following Transact-SQL statement in SQL Server Management Studio:
    SELECT [dbTest].[dbo].[XMLTest] ()
    You receive the correct result.

If you use a main assembly that references other assemblies, you must generate XML serialization assemblies for all the assemblies that are referenced by the main assembly. Then, you must load these XML serialization assemblies into the SQL Server database by using the CREATE ASSEMBLY statement.


This behavior is by design.


For more information about XML serialization from CLR database objects, visit the following MSDN Web site:For more information about the XML Serializer Generator tool, visit the following MSDN Web site: