Solved

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

Posted on 2010-11-24
3
3,922 Views
Last Modified: 2012-05-10
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...
0
Comment
Question by:rperryman
3 Comments
 
LVL 27

Assisted Solution

by:Chinmay Patel
Chinmay Patel earned 400 total points
Comment Utility
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
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 100 total points
Comment Utility
0
 

Author Closing Comment

by:rperryman
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now