I have a SQLCLR Function that serializes an object as XML that works fine on my local development machine (and our other DBA's machine as well), but when I try and run the same function on our test database server, I get the following:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetXml":
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.RuntimeAssembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection, SecurityContextSource securityContextSource)
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.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources) at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence) System.InvalidOperationException: at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence) at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies) at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace) at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace) at XmlExtensions.ToXml[T](T value) at UserDefinedFunctions.GetXml()
I have seen a lot of posts that say that you need to create a Assembly.XmlSerializers.dll, but if that's really the case, why does it work fine on our local machines without needing that DLL?
In order to make sure it wasn't something related to compiling the same code, I created a new SQL project, compiled it, and then just sent the SQL script to our DBA and he was able to run it without needing the XmlSerializers.dll.
using System;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
using Microsoft.SqlServer.Server;
public class UserDefinedFunctions
{
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.Read)]
public static SqlXml GetXml()
{
var x = new Test1 { S = DateTime.Now.ToString() };
return new SqlXml(new XmlTextReader(x.ToXml(), XmlNodeType.Document, null));
}
}
public class Test1
{
public string S { get; set; }
}
public static class XmlExtensions
{
public static string ToXml<T>(this T value)
{
if (value == null) return string.Empty;
var xmlserializer = new XmlSerializer(typeof(T));
using (StringWriter stringWriter = new StringWriter())
{
using (var writer = XmlWriter.Create(stringWriter, new XmlWriterSettings { Indent = true }))
{
xmlserializer.Serialize(writer, value);
return stringWriter.ToString();
}
}
}
}
The dacpac from this project loaded fine on the other developer's machine, but failed for the same reason on our test server. I haven't found anything telling me why this would work on some machines but not on others.
