Consuming Web Services from the SQL CLR

Hi there,

I've been following Vineets and David's procedures to consume web

services using SQL CLR to the t. I created my web service in C#.NET

2005, and generated my proxy using this command:

wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx

I added both files to the project, set Generate Serialization Assembly to on and compiled it.
I then generated a strong name key for the assembly and signed my assembly with that key.
Inside my post-build event I added the following script:

"E:\Development\Microsoft

Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force

/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures

$(TargetDir)$(TargetName).dll

This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.

In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my

first assembly with permissions EXTERNAL ACCESS and then the

XmlSerializer assembly with permissions SAFE. I created my stored

procedure and ran it. When I did I got this error which I assumed the

XmlSerializer was supposed to solve for me:

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.

I have seen alot of posts about this error, but none of them has been able to solve my problem.

Please can you help me?

O'Connor

Hi O'Connor,

As you have experienced, there are a few cases where sgen does not solve the problem of dynamically loading the XmlSerializer assembly.There is usually always a possible workaround, but it can vary depending on exactly what your code is doing.I was intending on writing some more blog posts about solutions to these dynamic assembly loading problems, but haven't had the time to finish them yet.

The problem you're most likely hitting is that the code doing the XmlSerialization uses an XmlSerializer constructor that does not check for the pre-generated (sgened) assemblies.This could be either directly in your code, or in other .NET Framework code that does this, such as in a strongly typed ADO.NET Dataset.This is covered in the msdn documentation for XmlSerializer if you look at the "Dynamically Generated Assemblies" section http://msdn2.microsoft.com/en-us/library/system.xml.serialization.xmlserializer.aspx

If this is indeed the problem, then the easiest way to fix this is to modify your code to instead use one of the constructors that does check for a pre-generated XmlSerializer assembly.This might be more difficult if it is not your code that is directly calling the XmlSerializer constructor.If you can include the full callstack of the error message you shared below and a snippet of the code where the XmlSerialization is happening, I should be able to help you fix it.

Steven

|||Hi Steven,

Thanks for getting back to me in such a short time.
I was playing with it last night and finally got it working.
My types aren't complex, I only use strings within my assembly (as it is a sms service)
What I did eventually do to fix it was to remove the post build script and set the "Generate Serialization Assembly" on the build tab of the project properties to ON.
It seems that using the sgen tool, the serialization assembly wasn't generated exactly the way that the CLR required it.

I found this website to be very useful in the end:
http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Regards,
O'Connor

|||

Hello Steven,

I am currently facing exactly the same strange problem as the initiator of this thread. I want to call a simple webservice from a stored procedure in SQL 2005. I've already tried the different ways of getting the serializer assemblies (using sgen and the build option in VS 2005). I also registered both assemblies one after another in in my SQL 2005 database. The db is trustworthy, assemblies are both marked with "external_access".

But I keep getting the Dynamic Load exception. In your previous post you said you could help if you have the full callstack; here it is...

Msg 6522, Level 16, State 1, Procedure CreateNotification, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'CreateNotification':
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[] sources)
at System.CodeDom.Compiler.Code
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, compilerParameters 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.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at MAN.Applications.HFIF.Database.ClrExtensions.WebServiceSoapClient..ctor(String ...

How can I change the behaviour of my soap client class to use a particular XmlSerializer constructor that checks for pre-generated assemblies? Could you please give me a hint?

Thanks very much for your help. Frank