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
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.
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
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()
...
Any hints?