Using web service in CLR Stored Procedure

Question

Post reply

Using web service in CLR Stored Procedure

brandon_rohrer

Old Hand

Points: 352
More actions
June 3, 2009 at 11:25 am

#210232

I have a CRL stored procedure which makes a call to a web service. Because I have a web service call I believe I have to run sgen.exe or roll my be xmlserializer.
Currently when I run my procedure I am getting the following error:
Msg 6522, Level 16, State 1, Procedure CallWebService, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'CallWebService':
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.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] sources)
at
...
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()
...
I then tried to run sgen.exe because that is what microsoft said I should do (http://support.microsoft.com/kb/913668).
When I run sgen.exe tool I get the following error:
Setting environment for using Microsoft Visual Studio 2008 x86 tools.
C:\Program Files\Microsoft Visual Studio 9.0\VC>sgen.exe /force C:\Projects\Pda\
Padls\Palims\src\LimbsSqlServer\bin\LimsSqlServer.dll
Microsoft (R) Xml Serialization support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.3038]
Copyright (C) Microsoft Corporation. All rights reserved.
Error: Generated serialization assembly is not signed: LimsSqlServer.XmlSerializ
ers, Version=1.0.3441.21819, Culture=neutral, PublicKeyToken=null. Please specif
y strong name key file via /compiler:/keyfile: switch.
Generated serialization assembly for assembly c:\projects\pda\padls\palims\src\l
imbssqlserver\bin\limssqlserver.dll --> 'c:\projects\pda\padls\palims\src\limbss
qlserver\bin\LimsSqlServer.XmlSerializers.dll.Deleted'.
C:\Program Files\Microsoft Visual Studio 9.0\VC>
We need to have the database option trusted off, so we have a ASYMMETRIC Key which is used to sign the assembly.
How can I get the sgen.exe to also sign the assembly its creating or is there a better way to this?
Thank you,
Brandon

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Create a keyfile and then use the Assembly linker to sign the assembly with the key file.

http://msdn.microsoft.com/en-us/library/xwb8f617.aspx

brandon_rohrer Old Hand Points: 352 More actions · Answer 2

I can't sign the assembly because sgen.exe will not create it because its not signed. My assembly which contains the CLR stored procedures is signed and I am using sgen.exe to create my xmlserializer.dll. If sgen.exe would create the dll I could sign it afterwards, but it won't create it.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Ok. I misunderstood your problem. You don't have to sign the serialization assembly. How are you signing your original assembly? Validate that:

1) In the project where you are adding a web reference got to Project->Properties (or hit Alt+F7)

2) Go to "Signing" tab

3) Check the "Sign the assembly" checkbox

4) And create a new key (this is one of the options)

Then rebuild the assembly and try using SGEN again.

Using web service in CLR Stored Procedure

Cookies on SQLServerCentral