SQL Server 找不到序列化程序集 [英] SQL Server not finding serialization assembly

问题描述

我正在尝试部署一个 UpdateContries SQL CRL 程序，它调用 Web 服务，遵循此处找到的帮助

I'm trying to deploy an UpdateContries SQL CRL Procedure which calls a Web Service, following help found here

• 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 服务.

So basically I have a Visual C# SQL CLR Database Project on Visual Studio 2010 with a simple Procedure that calls an external Web Services which was added as a Web Reference.

我已将项目复制到保存 SQL Server 2008 数据库的远程服务器.

I've copied the project to the remote server that holds the SQL Server 2008 database.

在项目属性中，我将生成序列化程序集设置为开"，将数据库权限级别设置为外部"，然后我部署代码已激活.

In the project properties I've set the Generate serialization assembly to "On", set the Database Permission Level to "External" and I have Deploy Code activated.

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

Also in the project I manually create and drop the serialization assembly as follows:

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

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

The project Build and Deploys successfully, I can see both ReportsWebServices and ReportsWebServicesXML in the Visual Studio 2010 Server explorer under the Assembies folder but when I attempt to run the procedure it still return the following error:

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()
...

有什么提示吗?

推荐答案

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

I found the solution here... http://www.cstruter.com/blog/320

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

"When the serialization setting is set to auto/on and you're consuming a webservice in your project, a serialization assembly automatically gets generated in your output folder (e.g. SourceAssembly.XmlSerializers.dll) which you'll need to register in SQL like seen in the following snippet:

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]"

这篇关于SQL Server 找不到序列化程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！