The problem lies in when the code calls
XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyClass));
where MyClass is a generated schema class using XSD. Since it wouldn't auto generate the serializer assembly, what I did instead was created a new class library project with only that line of code and provided the class definition within that same class without any of the attributes from the XSD. I am still getting an error on that same line. So what I did next was do the manual sgen and made sure it generated with the same signed assembly snk file. Manually loaded into sql server and still got the same error. Any ideas why this is?
Code Snippet
public partial class TestClass
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void testThis()
{
MyOwnClass myClass = new MyOwnClass();
//Serialize message to xml
XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyOwnClass));
}
}
public class MyOwnClass
{
private string hi;
public MyOwnClass()
{
hi = "1";
}
public string Hi
{
get
{
return hi;
}
}
}
Your code works fine for me. Are you sure you are creating the XmlSerializers assembly correctly under SQL Server? When you turn "Generate Serializaton Assembly" to on under VS, it will create the assembly but not deploy it to the database for you, so you need to create it yourself like so:
create assembly [TestClass.XmlSerializers] from 'c:\assemblies\TestClass.XmlSerializers.dll'
Also, what is the exact error message and call stack you are getting?
Steven
|||In order to get Visual Studio to actually build a serialization assembly, you have to both
1. Set the "Generate Serialization Assembly" to ON, and
2. Add, by hand, the following SGEN task to your project file:
<Target Name="GenerateSerializationAssembliesForAllTypes" DependsOnTargets="AssignTargetPaths;Compile;ResolveKeySource" Inputs="$(MSBuildAllProjects);@.(IntermediateAssembly)" Outputs="$(OutputPath)$(_SGenDllName)">
<SGen BuildAssemblyName="$(TargetFileName)" BuildAssemblyPath="$(OutputPath)" References="@.(ReferencePath)" ShouldGenerateSerializer="true" UseProxyTypes="false" KeyContainer="$(KeyContainerName)" KeyFile="My_Key_File.snk" DelaySign="$(DelaySign)" ToolPath="$(SGenToolPath)">
<Output TaskParameter="SerializationAssembly" ItemName="SerializationAssembly" />
</SGen>
</Target>
There is, however, a major problem with this approach: The SGEN task will only execute AFTER all post-build events have fired. Thus, if you have a post-build event that copies the serialization assembly to, say, a deployment folder, it will copy the old one because the copy occurs before the SGEN task is executed. This problem is best resolved by running SGEN from a post-build event like so:
sgen /a
But this is where your problems really begin. The only way I have been able to get SQL CLR to actually load the serialization assembly is to apply the XmlSerializationAssembly attributes to my serializable classes AND load the serialization assemblies into the GAC and SQL CLR. I have described this in detail in a previous post and it is the ONLY way I am able to invoke XML serialization from within a SQL CLR procedure.
Developing SQL CLR components is substantially more difficult than it needs to be due to issues just like this - and documentation is almost impossible to find for all but the most trivial use cases. I was very excited about the integration of SQL Server and .Net. After having worked a bit with this technology though, I am much less enthusiastic....
|||Steve - the assembly is not getting generated with the "Generate Serializaton Assembly" set to On. What I have been doing is like what NTDeveloper said (manually using SGEN). I then manually deploy that serializer assembly into SQL.The error message i'm getting is the typical one that everyone sees
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[])
NTDeveloper - I can do it the way you mentioend in your post, but I don't need the XmlSerializationAssembly attributes, I just need to deploy it to GAC. I don't like this approach however...
|||
TCHEN,
You are absolutely correct; I too was able to get the assemblies to load by ONLY putting them into the GAC and NOT attaching the XmlSerializationAssembly attribute. Now how in the heck are we going to get this to work without putting these things in the GAC ? This is also something I would very much like to avoid. According to Steve, SQL CLR will not load assemblies from the GAC unless the are on an internal, predefined list controlled by Microsoft. Obviously though, the GAC somehow plays a role here though; otherwise, the installation of these assemblies to the GAC would be irrelevant. Moreover, as I noted in another post, if the MVID (which gets regenerated each build) of the assembly in the GAC does not match the MVID assembly loaded into SQL CLR, you will get an exception that informs you of this.
I am at a loss.
|||I believe I have figured out what the problem is. The XmlSerialization assembly binding code will only load a strong-named assembly if it has an Assembly Version specified. I'm really not sure why this is, but I'll try to find out. If there is no Version specified, then the sgened assembly will not be found and it will try to load one dynamically, which of course fails. As NTDeveloper found, one workaround for this is to add the versionless .XmlSerializers assembly to the GAC. Note that the actual XmlSerializers assembly will still be loaded from the database, the gac check occurs before the assembly attempts to load (I believe this check is done because the CLR can skip verifying the assembly strong name if it is loaded from the GAC).
So, in short, in order to use XmlSerialization on a signed assembly in SQL CLR, your assembly must be annotated with an assembly version:
[assembly:AssemblyVersion("1.2.3.4")]
[assembly:AllowPartiallyTrustedCallers]
public class ...
(AllowPartiallyTrustedCallers is also required for Strong Named assembly, as per http://blogs.msdn.com/sqlclr/archive/2006/06/22/643554.aspx).
Hopefully this solves your problem. There is certainly no argument from me that this is a pretty complicated process.
Steven
|||Steven,
Thanks for your response.
Unfortunatey, I don't believe the version of the assembly is the issue. When SGEN generates a serialization assembly S for an assembly A, it automatically versions S with the version of A. Thus, since all of my assemblies are versioned the serialization assemblies are versioned as well.
Any other ideas?
Thanks,
Chris
|||Hmm, no I was sure it was going to be the AssemblyVersion issue as that is the only case I could see that fits your repro. Without a repro that I can debug, I'm really not sure what else could be causing this behavior. Can you try setting the Fusion ForceLog on and checking the Fusion Log to see what the output is when it tries to load the XmlSerializers assembly? This log should show exactly why it failed to load the pregenerated assembly.Steven|||
You actually may end up being right about the assembly version. Here's the deal:
I have FINALLY managed to successfully invoke XML serialization from INSIDE SQL Server WITHOUT installing to the GAC !!! Other than the general difficulty of running managed code inside SQL Server (which, by the way, is considerably more difficult than it should be), I believe that the root of the problem was using Visual Studio to build the serialization assemblies via the SGEN build task. Although I changed my makefile, er, project file, to use the sgen.exe utility to generate the assemblies, I was still inadvertantly using the SGEN build task and overwriting the assemblies generated via sgen.exe. Once I completely wiped out the SGEN build task and relied only on the sgen.exe to build my serialization assemblies, everything began to work like MAGIC. Voila! No more need to deploy to the GAC, thus no need to deploy FROM the server, etc, etc. It may be that the SGEN build task manages to create unversioned assemblies; however, since I have completely removed these build tasks and the assemblies generated by it, I can't say for sure. At some point, I am going to create a test project and see whether this is the case and I will post a follow-up here indicating the results.
Thanks for your help on this misery-inducing problem!
|||NTDeveloper,I'm a little confused. When you say SGEN build task, do you mean you set "Generate Serialization Assemblies" to ON?
Thanks.
|||Steve,
I followed NTDeveloper's advice and started to manually sgen the serializer assembly instead of installing it to the GAC, and i'm still getting an error. Here is the latest stack trace.
here is my sgen command
"$(DevEnvDir)..\..\SDK\v2.0\bin\sgen.exe" /f /a
A .NET Framework error occurred during execution of user-defined routine or aggregate "SendNotificationEmail":
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.CShar
...
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.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
|||
Yes; You have to set then "Generate Serialization Assemblies" to ON
AND
Manually define the SGEN build task within your project file like so:
<Target Name="GenerateSerializationAssembliesForAllTypes" DependsOnTargets="AssignTargetPaths;Compile;ResolveKeySource" Inputs="$(MSBuildAllProjects);@.(IntermediateAssembly)" Outputs="$(OutputPath)$(_SGenDllName)">
<SGen BuildAssemblyName="$(TargetFileName)" BuildAssemblyPath="$(OutputPath)" References="@.(ReferencePath)" ShouldGenerateSerializer="true" UseProxyTypes="false" KeyContainer="$(KeyContainerName)" KeyFile="My_Key_File.snk" DelaySign="$(DelaySign)" ToolPath="$(SGenToolPath)">
<Output TaskParameter="SerializationAssembly" ItemName="SerializationAssembly" />
</SGen>
</Target>
HOWEVER, as I indicated in the post above, a serialization assembly generated in this fashion will not load correctly inside SQL CLR. As Steve suggested, it may be a versioning issue; I just don't know. I haven't gone back and generated an assembly in this fashion and checked the version information.
After using the SGEN.exe tool did you uninstall the previously generated assemblies from the GAC? If not, this may be the reason why you can't get the new serialiazation assembly to load properly. After I switched to using the SGEN.exe tool, instead of the Visual Studio build task, this issue hasn't recurred.
|||Yes I checked the GAC...its empty. I had Generate Serialization to OFF since i'm using the SGEN.exe tool rather than doing it through visual studio. Even with this manner its failing.If I do it with an empty task with XmlSerializer = new XmlSerializer(MyClass) however..that works. But with the use of making a webservice call, it fails with the error above.
btw the Fusion log only logs exceptions to disk from visual studio it seems. none of my sql clr stuff is getting logged.
|||
I was also unable to get the Fusion tool to work for SQL CLR.
Have you used the SGEN.exe tool to create a serialization assembly for your SOAP proxy class(es)?
|||Can you post the code you're using to call the webservice? Are you sure the error occurs while attempting to serialize MyOwnClass vs. a different class? It seems the XmlSerializer assembly can be loaded correctly for MyOwnClass, so perhaps the problem is elsewhere (such as trying to serialize a class shipped in a system assembly).
Steven
