SQL Server not finding serialization assembly

serializationsql-server-2008sqlclrvisual studio 2010web services

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

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?

Best Answer

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

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