SGEN gives "Assembly ... does not contain any types that can be serialized using XmlSerializer.

The other day I was working a case where a SQL CLR User Defined Function was used.

A bit more on this here: "How to: Create and Run a CLR SQL Server User-Defined Function"

https://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx

and in this case customer was sending back results from a web service, this was done by serializing the results and sending the data back as XML.

When doing this, you will probably run into a certain type of error (which customer did, I will add an entry on this later) to which the solution is to run SGEN on the built dll.

So, I started the research by creating a new SQL Server Project (called it SgenTest) and I selected the database that I wanted to deploy to.

Then I added a new User Defined Function called MyFunction, the generated code will look like this:

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString MyFunction()

    {

        // Put your code here

        return new SqlString("Hello");

    }

};

Then I built the project which generated my .dll (SgenTest.dll) and I was now to run SGEN on it. I did this by using the same syntax as customer.

So, I fired up the “Visual Studio 2008 Command Prompt” and navigated to where the SgenTest.dll was located and ran the following:

C:\Path>sgen /f /p SgenTest.dll

Which gave me:

Microsoft (R) Xml Serialization support utility

[Microsoft (R) .NET Framework, Version 2.0.50727.3038]

Copyright (C) Microsoft Corporation. All rights reserved.

Assembly 'C:<path>\sgentest.dll' does not contain any types that can be serialized using XmlSerializer.

Please use /verbose switch for more information.

 

Ok, adding the verbose output probably would give something, so reran with that switch.

C:\Path>sgen /f /p /v SgenTest.dll

This just gave me the same error. So, did some research and basically found nothing, this is why I’m writing this post.

I then realized that customer was using a web service and therefore was using the /p switch.

This switch means the following: “Generates serialization code only for the XML Web service proxy types.”

"XML Serializer Generator Tool (Sgen.exe)"

https://msdn.microsoft.com/en-us/library/bk3w6240.aspx

So what was happening here was that since I ran with the /p switch it ignored my own code. And because of that it obviously couldn’t find any types that could be serialized.

In short, if you are not using a XML Web service proxy, then do not use /p since this will cause sgen to only try to generate the serialization code for that.

C:\Path>sgen /f SgenTest.dll

Gave me the SgenTest.XmlSerializers.dll that I was looking for.

HTH.