call web service in Sql server

• chatwithkrishan

  Ten Centuries

  Points: 1056

  More actions

  June 2, 2009 at 8:41 am

  #384700

  hi all,

  I created web service, which accepts list as input and return a string as out put.

  i want to call this web service in sql server. i got error.

  what i did -->

  i created a userdefined function in vs 2005.i added the web service.

  using System;

  using System.Data;

  using System.Data.SqlClient;

  using System.Data.SqlTypes;

  using Microsoft.SqlServer.Server;

  using System.Xml;

  using ABCFunctionNew.ABCServ;

  public partial class UserDefinedFunctions

  {

  [Microsoft.SqlServer.Server.SqlFunction]

  public static SqlString FunctionABC(String first_name, String last_name)

  {

  ABCService ABC = new ABCService();

  string[] myStringArray = new string[3];

  myStringArray[0] = first_name;

  myStringArray[1] = last_name;

  return ABC.GetABC(myStringArray).ToString();

  }

  };

  In the project properties --> in the build Events -->post build event command i added this.

  "D:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"

  In the Build -->Generate serialization assembly-- >set on

  in the database --> Permission level -External

  i build and run the project

  i got two assembly in my bin folder.

  one is project assemly and another is serialization assembly.

  in the sql server, i can see the user defiinded funtion FunctionABC()

  also the assemblies.

  when i call the function i got error :

  Msg 6522, Level 16, State 2, Line 1

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

  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

  ...

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

  how to resolve this ???

• gtschech

  Ten Centuries

  Points: 1351

  More actions

  June 3, 2009 at 6:56 am

  #1003954

  Hi,

  your problem is related to the fact that the call new XmlSerializer(typeof("yourtype")) by default tries to create a .NET assembly on the fly, saves it to some temporary location and loads it into it's process space.

  This is fine with most .net apps, but SQL server is much more restricted due to its enterprise readiness and does not allow this kind of changing program code "on the fly".

  So your only choice is to create a Xml Serialization assembly on compile time and load it into SQL Server as well.

  By default, the XmlSerializer class looks for an assembly with the name of the assembly that holds the "mytype" class with the appendix "XmlSerializers", so if your assembly is called Company.Product.MyAssembly you would supply an assembly called Company.Product.MyAssembly.xmlSerializers.

  I guess such an assembly is already been generated by your build process.

  Unfortuantely, I cannot give you detailed advice because I have no sample at hand.

  WM_JUSTMY2CENTS

  Guenter