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"
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
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)"
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.