Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
4,283 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 1200 total points
ID: 34210963
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 300 total points
ID: 34221020
0
 

Author Closing Comment

by:rperryman
ID: 34372497
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

916 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