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”


http://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)” 


http://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.


 


Comments (0)