Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> .NET Framework inside SQL Server

Consuming Web Services In Sql CLR

I am using SQL Server June CTP and I have a CLR stored procedure that is consuming a web service that has 3 methods:
1) GetXml - returns xml data as string.
2) GetXsd - returns xsd as string.
3) GetData - returns a dataset.

I am only using the GetData method to retrieve data and do some processing in the stored procedure. When I try to deploy the assembly, I get the following error:

CREATE ASSEMBLY failed because method "add_GetXmlCompleted" on type "SqlServerAssembly.EsoDataWebService.ESODataSet" in external_access assembly "SqlServerAssembly" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. SqlServerAssembly

Am I trying to do something over here that's not possible or not allowed in Sql CLR? Thanks!

[867 byte] By [PinalPatel] at [2008-2-13]

«« Copy and Past Excel spreadsheet
»» Setup Project Bug?

# 1

Deploy your assembly as UNSAFE, instead of EXTERNAL_ACCESS. That will take care of that issue.
Niels

nielsb at 2007-9-9 >

top of Msdn Tech,SQL Server,.NET Framework inside SQL Server...

# 2

I tried deploying the assembly as UNSAFE and now I am getting this error:

Could not load file or assembly '1316 bytes loaded from Microsoft.VisualStudio.DataTools, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. An attempt was made to load a program with an incorrect format.

PinalPatel at 2007-9-9 >

# 3

I guess you have an app.config that is causing this issue. Remove that from your project and deploy again.
To make it work in external access follow the steps in the blog:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

Thanks,
-Vineet.

VineetRao-Microsoft at 2007-9-9 >

# 4

Thank you Vineet! I was able to deploy the assembly with 'Unsafe' permission level after deleting the app.config file. But, now when I try executing that stored procedure from Management Studio, I get the following error:

Msg 6522, Level 16, State 1, Procedure TestSproc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'TestSproc':

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[] source

...

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 SqlServ...

PinalPatel at 2007-9-9 >

# 5

For XML Serialization (required for calling web services) you need to pregenerate the serializer assembly and register it in the database. You can generate the serialization assembly using a tool called sgen that is shipped with .NET Framework SDK.

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies - myAsm.dll and myAsm.XmlSerializers.dll, you need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE
To automate this in visual studio, follow the instructions in the blog: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
Thanks,
-Vineet.

VineetRao-Microsoft at 2007-9-9 >

# 6

Thank you once again Vineet! Now the stored procedure executes without any errors!

PinalPatel at 2007-9-9 >

SQL Server

.NET Framework inside SQL Server

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Question about 'Consuming Web Services In Sql CLR', No.73977,Posted by PinalPatel

Consuming Web Services In Sql CLR

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Site Classified