visual-studio-2010 - SQL Server 未找到序列化程序集

Question

我正在尝试部署调用 Web 服务的 UpdateContries SQL CRL 过程，遵循此处的帮助

• http://www.elzaris.co.za/using-web-services-in-sql-server-clr-assemblies/
• http://msdn.microsoft.com/en-us/library/84b1se47.aspx
• footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx

所以基本上我在 Visual Studio 2010 上有一个 Visual C# SQL CLR 数据库项目，它有一个简单的过程调用外部 Web 服务，该服务被添加为 Web 引用。

我已将该项目复制到包含 SQL Server 2008 数据库的远程服务器。

在项目属性中，我将Generate serialization assembly设置为“On”，将Database Permission Level设置为“External”，并且我已经Deploy代码激活。

同样在项目中，我手动创建和删除序列化程序集，如下所示:

PreDeployScript.sql

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'ReportsWebServicesXML')
    DROP ASSEMBLY [ReportsWebServicesXML];
GO

PostDeployScript.sql

CREATE ASSEMBLY [ReportsWebServicesXML]
    FROM 'E:\Projects\Reports\ReportsWebServices\ReportsWebServices\bin\Debug\ReportsWebServices.XmlSerializers.dll'
    WITH PERMISSION_SET = SAFE;
GO

项目构建和部署成功，我可以在 Assembies 文件夹下的 Visual Studio 2010 服务器资源管理器中看到 ReportsWebServices 和 ReportsWebServicesXML 但是当我尝试运行该过程时它仍然返回以下错误:

Msg 6522, Level 16, State 1, Procedure UpdateContries, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateContries": 
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.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] 
...
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

我在这里找到了解决方案... http://www.cstruter.com/blog/320

“当序列化设置设置为 auto/on 并且您在项目中使用 Web 服务时，序列化程序集会自动生成在您需要注册的输出文件夹中(例如 SourceAssembly.XmlSerializers.dll)在 SQL 中，如以下代码片段所示:

CREATE ASSEMBLY CLRProcedures FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.dll' 
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY CLRSerializer FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.XmlSerializers.dll' 
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE Test
AS EXTERNAL NAME [CLRProcedures].[CSTruter.com.StoredProcedures].[Test]"