Error deploying a CLR Stored Procedure that uses a web service

Ok, first some background.

I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.

This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.

At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:

<code>

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, S

</code>

Anyone have any ideas?

Thanks!

Dave Borneman

Solution Architect,

anyWare Mobile Solutions.

OK, so when you are using Web services the .NET framework will dynamically generate an assembly based on your proxy code. Dynamic assembly generation is not allowed inside SQL Server (SQLCLR).

What you need to do is to pre-generate the proxy assembly by using the sgen tool and deploy that generated assembly into the database.

Look at this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74480&SiteID=1 and specifically the 6:th message in that thread how to achieve the pre-generation.

Niels