我正在尝试部署一个调用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数据库的远程服务器上。
在项目属性中,我设置生成序列化程序集为“开启”,将数据库权限级别设置为“外部”,并激活了部署代码。
此外,在项目中,我手动创建和删除了序列化程序集,如下所示:
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 服务器资源管理器的 Assemblies 文件夹下看到 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()
...
有什么提示吗?