Microsoft KB Archive/913668

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

Article ID: 913668

Article Last Modified on 5/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



Bug #: 101935 (SQLBUDT)



SYMPTOMS
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.

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)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] s

...

System.InvalidOperationException:

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.



CAUSE
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 &quot;Indigo&quot;) 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 &quot;Symptoms&quot; 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:

http://msdn2.microsoft.com/en-us/library/ms735119.aspx

You may receive the error message that is mentioned in the &quot;Symptoms&quot; 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.



RESOLUTION
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 {   [Microsoft.SqlServer.Server.SqlFunction] public static SqlString XMLTest {       Person p = new Person; return new SqlString(p.GetXml); }   public class Person {        public String m_FirstName = &quot;Jane&quot;; public String m_LastName = &quot;Dow&quot;;

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:   Jane Dow  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:  Start Visual Studio 2005. Create a new SQL Server project that is named MyTest. 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.</li> On the Project menu, click Add User-Defined Function. The Add New Item dialog box appears.</li>  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 &quot;Symptoms&quot; 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. </li> Add the code that is listed in the &quot;Code example&quot; section to the Function1.cs file.</li> On the Project menu, click MyTest Properties.</li> On the MyTest dialog box, click the Build Events option.</li> Type the following command in the Post-build event command line box:

&quot;C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe&quot; /force &quot;$(TargetPath)&quot;

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.</li> In the C:\CLRTest folder, create two text files that are named Predeployscript.sql and Postdeployscript.sql.</li>  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] </li>  Add the following Transact-SQL statements to the Postdeployscript.sql file: CREATE ASSEMBLY [MyTest.XmlSerializers] from 'C:\CLRTest\MyTest\MyTest\bin\Debug\MyTest.XmlSerializers.dll' WITH permission_set = SAFE </li> On the Project menu, click Add Existing Item.</li> In the Add Existing Item dialog box, locate the C:\CLRTest folder, and then click All Files (*.*) in the Files of type list.</li> In the File name box, type Postdeployscript.sql;Predeployscript.sql, and then click OK.</li> On the Build menu, click Deploy MyTest.</li>  Run the following Transact-SQL statement in SQL Server Management Studio: SELECT [dbTest].[dbo].[XMLTest] You receive the correct result. </li></ol>

Method 2: Build a SQL CLR project at the Visual Studio Command Prompt window
<ol> Locate the C:\CLRTest folder.</li> <li>Create a text file that is named MyTest.cs.</li> <li>Add the code that is listed in the &quot;Code example&quot; section to the MyTest.cs file.</li> <li>Open the Visual Studio 2005 Command Prompt window.</li> <li>Type CD C:\CLRTest, and then press ENTER.</li> <li>Type csc /t:library MyTest.cs, and then press ENTER.</li> <li>Type sgen.exe /force MyTest.dll, and then press ENTER.</li> <li> Run the following Transact-SQL statements in SQL Server Management Studio: USE dbTest GO CREATE ASSEMBLY [MyTest] from 'C:\CLRTest\MyTest.dll' GO CREATE ASSEMBLY [MyTest.XmlSerializers.dll] from 'C:\CLRTest\MyTest.XmlSerializers.dll' GO

CREATE FUNCTION XMLTest RETURNS nvarchar (max) AS EXTERNAL NAME MyTest.StoredProcedures.XMLTest GO </li> <li> Run the following Transact-SQL statement in SQL Server Management Studio: SELECT [dbTest].[dbo].[XMLTest] You receive the correct result. </li></ol>

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.

<div class="status_section">

STATUS
This behavior is by design.

<div class="references_section">