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!

Deploy your assembly as UNSAFE, instead of EXTERNAL_ACCESS. That will take care of that issue.
Niels
|||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.|||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.|||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...

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

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