SQL CLR Procedure Does not connect Webservice

Question

Hi,

I have tried to connect the webservice from SQL CLR. For that I have created a SQLProject in Visual Studio 2010 with the framework of 2.0. And I refered my service as Web Reference. If I am using it in Console application it works fine. But when I tried to access it from my SQL CLR Project it says below error.

Msg 6522, Level 16, State 1, Procedure SQLCLR, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "SQLCLR":
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.CompileAssemblyFromSourceBat
    ...
System.InvalidOperationException:
   at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
   at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters 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.GetSerializersFromCache(XmlMapping[] mappings, Type type)
   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 SpecimenSevenSQLCLR.PeopleWS.Peo...

I tried to deploy the below ways,

EXEC sp_configure 'show advanced options', 1
GO
--- To update the currently configured value for advanced options.
RECONFIGURE
GO
sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

ALTER DATABASE MyDB29June SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY [SpecimenSevenSQLCLR]
from 'D:\SQLCLR\SpecimenSeven\SpecimenSevenSQLCLR.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [SpecimenSevenSQLCLR.XmlSerializers.dll]
 
 from 'D:\SQLCLR\SpecimenSeven\SpecimenSevenSQLCLR.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE [dbo].[SQLCLRProcedure]

AS EXTERNAL NAME [SpecimenSevenSQLCLR].[StoredProcedures].[SpecimenSevenSQLCLR]

But even I set my database trust worthy, it says the above error when I execute the StoredProcedure.

Can anyone guide me on this......  ASAP...

Regards,

Saravanan R

---

Regards, Sarav Raj!

Answer 1

Hi Sarav,

Please search the many previous posts on this topic in this forum for a plethora of information on what causes this error message, and solutions and workarounds. Short answer: NGen.exe is your friend...

Cheers, Bob

Answer 2

Check you other post http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/e318bbd0-1925-431c-8aac-d3ff9be2dd59

---

everything is a matter of probability...