How to fix 'Cannot load dynamically generated serialization assembly' error

Question

"Cannot load dynamically generated serialization assembly." error when executing a clr function that calls a web service in SQL server 2008 R2.

I have a database project in Visual Studio 2017 which includes a clr function that calls a web service.

The project properties include the following:

Project Settings Target Platform = SQL Server 2008

SQLCLR Target framework = .Net Framework 3.5

SQLCLR Build Generate serialization assembly = On

The serialization assembly is created in the database by a post-deployment script when the database is published. When I publish the databse to a SQL Server 2008R2 instance on my Windows 10 PC, executing the clr function results in the "Cannot load dynamically generated serialization assembly" error, however, when the database is published to a SQL2016 instance on my PC, it runs OK

Post deployment script to register serialization assembly:

CREATE ASSEMBLY [CifasEdit.XmlSerializers] FROM 'C:\tfs\CIFAS\Source\Database\Hub\CifasEdit\CifasEdit\bin\Release\CifasEdit.XmlSerializers.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

The full error is here

A .NET Framework error occurred during execution of user-defined routine or aggregate "xxx": 
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.
    ...
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()

Liz Nett · Accepted Answer · 2019-02-01 16:05:22Z

2

So it appears that the serialization assembly generated automatically by VS2017 does not play nice with SQL Server 2008 R2 although it works fine with SQL2016.

To get the CLR function working in 2008R2 I moved the CLR code to a separate project and generated the serialization assembly manually using sgen.exe, then registered both dlls.

Be sure to use a version of sgen for clr2.0, on my PC this was located in

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin

The command to generate the serialization assembly is

sgen.exe c:\YourDir\YourAssembly.dll

This will create an assembly called YourAssembly.XmlSerializers.dll Register both assemblies with SQL Server:

CREATE ASSEMBLY [YourAssembly] FROM 'c:\YourDir\YourAssembly.dll' WITH PERMISSION_SET  = EXTERNAL_ACCESS

CREATE ASSEMBLY [YourAssembly.XmlSerializers] FROM 'c:\YourDir\YourAssembly.XmlSerializers.dll' WITH PERMISSION_SET = SAFE

answered Feb 1, 2019 at 16:05

Liz Nett

1492 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Solomon Rutzky Over a year ago

Thanks for posting the solution. I remembered seeing this a while ago somewhere and had finally just found it (a SQL Server Central forum), but I guess I didn't find it soon enough :(. It is the exact same issue but with SQL Server 2014 and Visual Studio 2012. I suspect there was a change in CLR 4 (used by SQL Server 2012 and newer) from CLR 2 (used by SQL Server 2005, 2008, and 2008 R2) that accounts for this.

Niels Berglund Over a year ago

Didn't see this until now. It is always best practice to generate the serialization assembly separately, and not use whatever VS creates.

Panagiotis Kanavos Over a year ago

@NielsBerglund when VS creates a serialization assembly, it uses SGEN. By default it doesn't create one. Serialization assemblies are generated dynamically at runtime, unless you force their generation with an extra build step. That's a common practice with high traffic web/REST services to reduce cold start time

Panagiotis Kanavos Over a year ago

@LizNett VS doesn't generate any serialization assembly. The serialization assemblies are generated dynamically when an application starts. To avoid this you have to explicitly generate the serialization assembly by calling sgen in a post-build step

Liz Nett Over a year ago

@PanagiotisKanavos As an alternative to calling sgen in a post-build step, you can also set the project property SQLCLR Build Generate serialization assembly = On and a serialization assembly will be generated when the project builds. The problem I had was that the serialization assembly generated this way did not work in a SQL2008R2 database, as explained above.

|

Collectives™ on Stack Overflow

How to fix 'Cannot load dynamically generated serialization assembly' error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related