none
SQL Server not finding serialization assembly

    Question

  • Hello.

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


    Monday, February 27, 2012 12:57 PM

All replies

  •  set the Database Permission Level to "External"

    PostDeployScript.sql

    CREATE ASSEMBLY [ReportsWebServicesXML]

    FROM 'E:\Projects\Reports\ReportsWebServices\ReportsWebServices\bin\Debug\ReportsWebServices.XmlSerializers.dll'

    WITH PERMISSION_SET = SAFE;

    GO

    ...

    Looks like SAFE contradicts "External" .

    Serg


    • Edited by SergNL Monday, February 27, 2012 1:50 PM
    Monday, February 27, 2012 1:49 PM
  • SergNL, thank you for your prompt reply!

    According to this comment [ http://blog.hoegaerden.be/2008/11/11/calling-a-web-service-from-sql-server-2005/#comment-2231 ] the XMLSerializer assembly can be added with the SAFE access rule. The assembly that calls the WS should be the only one that needs external access.

    Still, I re-deployed ReportsWebServicesXML with EXTERNAL_ACCESS and the procedure returned the same error.

    There error is not "complaining" about permissions or access limitations, so I assume the problem should be somewhere else, no?

    Monday, February 27, 2012 2:18 PM
  • Did you enable CLR procedures on the server using option_reconfigure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 27, 2012 2:22 PM
    Moderator
  • One thing i don't understand, which way MSSS finds specific serialization assembly for a given assembly? Should both assemblies obey some naming conventions or what?

    Serg

    Monday, February 27, 2012 2:46 PM
  • I also think that the assembly name might be related to the problem.

    Here [ http://footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx ] the author points out that:

    Note that although the name of the serializers assembly is UserProcedures.XmlSerializers.dll, we must reference the assembly as UserProceduresXML, since <assembly>XML is the naming standard for the name of a serializer assembly in SQL Server.

    But I'm still yet to find someone confirming that statement.

    On the other side, there are other tutorials disregarding the assembly name, like this one on msdn: http://msdn.microsoft.com/en-us/library/84b1se47.aspx#CodeSnippetContainerCode_5990b7d9-30a6-4ccd-ba70-c4d4a7b44dbb

    Close to the end it states:

    In the code editor, add the following Transact-SQL statement to the script:
    
    CREATE ASSEMBLY SqlClassLibraryXML from 'path\assemblyname.XmlSerializers.dll' 
    
    Where SqlClassLibraryXML is that name that you want to give the assembly, path is the path to the serializer assembly, and assemblyname.XmlSerializers.dll is the name of the serializer assembly.
    Which mentions nothing regarding the chosen assembly name.

    Monday, February 27, 2012 3:55 PM
  • Did you enable CLR procedures on the server using option_reconfigure?

    Just to be sure I re-ran it again:

    exec sp_configure 'clr enabled', '1';
    reconfigure;
    
    -----------------------------------
    
    Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.



    • Edited by NelsonTC Monday, February 27, 2012 5:13 PM
    Monday, February 27, 2012 5:05 PM
  • Nope, no progress on this issue so far.

    Since my task can run outside the DB I'm implementing it as a service /scheduled task, but it's still a shame, I really wanted to be able to deploy a CLR on the DB... because sooner or later I'll be forced to implement it on the DB.

    Monday, March 05, 2012 4:02 PM
  • Friday, November 02, 2012 5:55 PM
  • This continues to be an issue.  Sad to see that there has been no response on it for such a long time, and sad to see so much conflicting information scattered around the web, much of it on Microsoft blogs and documentation.
    Wednesday, May 22, 2013 4:45 PM