Microsoft SQL server has built-in security which prevents the dynamic loading of serialized assemblies. What this basically means is that an assembly which utilises Webservices and XML.Serialization will dynamically load the code and compile at runtime and SQL Server doesn’t like that. This is the error you will receive when running functions or procedures in such an assembly.
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘RedMarsClass’:
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)
The error hints at what the solution is: “consider using pre-generated serializer.” The process to do this is in Visual Studio 2010 is to edit the properties of the SQL project. On the Build tab change the “Generate serialization assembly” option to on. This will generate an new assembly using sgen. The naming standard is as follows, if your standard generated dll is named redmarsproject.dll then the serialized assembly will be named redmarsproject.XmlSerializers.dll.
Now this isn’t the end of the story because the SQL project will not deploy the newly generated dll to the SQL server by default. This has to be manually added every time the project is redeployed to the SQL server. This can be achieved by either adding the following SQL code as a post-build event in the project if you are running visual studio on the target deployment SQL server, or by manually running it on the target deployment SQL server.
CREATE ASSEMBLY [RedMarsProject.XmlSerializers] FROM ‘
