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

RESOLUTION

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();
        }

    }
}

<?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>

• 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

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.