Cannot load dynamically generated serialization assembly. Help! Visual Studio 2010 SQL CLR utilizing a Web Service - SQL 2008 -

Hi Experts,

I am building a Stored Procedure in .NET utilizing SQL CLR.  The point of the SQL CLR is to connect to a Web Service.  After I deploy I get the error following this message when attempting to run the Stored Procedure.  

Everything I find on the net is not Visual Studio 2010 related.  I have options to Generate the Serialization Assembly in 2010 which I have set to On.  Permissions are UnSafe and I've tried Safe but it won't deploy if Safe. Most of the web says I have to run Create Assembly but the Assembly is in Assemblies on the Server.

Thanks for your help!


Msg 6522, Level 16, State 1, Procedure CreateSRAsync, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CreateSRAsync":
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(CompilerPar
      ...
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()
   at Dell.DeltaConnect.Pro...
rperrymanAsked:
Who is Participating?
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
0
 
Chinmay PatelConnect With a Mentor Enterprise ArchitectCommented:
Hi rperryman,

How about setting Generate the Serialization Assembly to Off? If it is a must for you then I suggest you create a separate assembly for serialization.
 
Regards,
Chinmay
0
 
rperrymanAuthor Commented:
Here's what I found to do... copy the compiled DLL's in your project debug or release folder and place them in a folder on the SQL server.  Then run this...

Drop procedure InternalStoredProcedureName
Drop assembly [ClassName.XmlSerializers]
drop assembly ClassName

create assembly ClassName
FROM 'C:\path\putyourdllyoucompiledhere.dll'
WITH PERMISSION_SET = UNSAFE;
go

CREATE ASSEMBLY [ClassName.XmlSerializers]
from 'C:\path\putyourdllyoucompiledhere.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

Create Procedure InternalStoredProcedureName( @Parameter1 nvarchar(max),
                                      @Parameter2 decimal(10,0),
                                      @Parameter3 nvarchar(max),
                                      @Parameter4 DateTime,
                                      @Parameter5 bigint,
                                      @Parameter6 bit
                                      )
AS EXTERNAL NAME ClassName.StoredProcedures.StoredProcedureNametoBeUsedinSQL

If this is the first time the drops can be commented out.

Garrett
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.