如何修复'Cannot load dynamically generated serialization assembly'错误

Question

执行调用SQL server 2008 R2中web服务的clr函数时出错。

我在Visual Studio 2017中有一个数据库项目，其中包括一个调用web服务的clr函数。

项目属性包括：

项目设置目标平台=SQL Server 2008

SQLCLR目标框架=.Net framework 3.5

SQLCLR生成序列化程序集=在上

序列化程序集是在数据库发布时由部署后脚本在数据库中创建的。当我将数据库发布到Windows 10 PC上的SQL Server 2008R2实例时，执行clr函数会导致"无法加载动态生成的序列化程序集"错误，但是，当数据库发布到PC上的SQL2016实例时，它会运行OK

用于注册序列化程序集的部署后脚本：

CREATE ASSEMBLY [CifasEdit.XmlSerializers] FROM 'C:tfsCIFASSourceDatabaseHubCifasEditCifasEditbinReleaseCifasEdit.XmlSerializers.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

完整的错误在这里

A .NET Framework error occurred during execution of user-defined routine or aggregate "xxx": 
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.CSharpCodeGenerator.System.CodeDom.
...
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.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
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()

Answer 1

因此，VS2017自动生成的序列化程序集在SQL Server 2008 R2中运行得并不好，尽管它在SQL 2016中运行得很好。

为了使CLR函数在2008R2中工作，我将CLR代码移动到一个单独的项目中，并使用sgen.exe手动生成序列化程序集，然后注册了两个dll。

一定要使用clr2.0的sgen版本，在我的电脑上，它位于中

C:Program Files (x86)Microsoft SDKsWindowsv7.0Abin

生成序列化程序集的命令是

sgen.exe c:YourDirYourAssembly.dll 

这将创建一个名为YourAssembly.XmlSerializers.dll的程序集向SQL Server注册两个程序集：

CREATE ASSEMBLY [YourAssembly] FROM 'c:YourDirYourAssembly.dll' WITH PERMISSION_SET  = EXTERNAL_ACCESS
CREATE ASSEMBLY [YourAssembly.XmlSerializers] FROM 'c:YourDirYourAssembly.XmlSerializers.dll' WITH PERMISSION_SET = SAFE