DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 913668 - Last Review: May 23, 2007 - Revision: 2.1

Bug #: 101935 (SQLBUDT)

On This Page

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 "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:
http://msdn2.microsoft.com/en-us/library/ms735119.aspx (http://msdn2.microsoft.com/en-us/library/ms735119.aspx)
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.

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 = "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="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<m_FirstName>Jane</m_FirstName>
<m_LastName>Dow</m_LastName>  
</Person>
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
    'C:\CLRTest\MyTest\MyTest\bin\Debug\MyTest.XmlSerializers.dll'
    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 OK.
  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
    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
  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.

STATUS

This behavior is by design.

REFERENCES

For more information about XML serialization from CLR database objects, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms131088.aspx (http://msdn2.microsoft.com/en-us/library/ms131088.aspx)
For more information about the XML Serializer Generator tool, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/bk3w6240.aspx (http://msdn2.microsoft.com/en-us/library/bk3w6240.aspx)

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
Keywords: 
kbsql2005clr kbexpertiseadvanced kbprb KB913668
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support