SQL 2005中用存储过程调用Web Services

转贴
SQL 2000中还未找到解决办法,但是因为SQL 2005本身就是在.Net架构上，所以支持存储过程中调用WSE

CLR procedure in SQL2K5 calling webservice

Posted by: Ludo Bernaerts on ?? 24, 2006 DIGG

Hi, I want to create following procedure to call a webservice. build ok execution not ok.
When i do it in a seperated program it works. in the clr procedure not.
It always end with 'System.InvalidOperationException' occurred in System.Xml.dll

Can some one help me.
Ludo

SQL code:
exec dbo.SendStatusToWebservice 'SQL2K5','TEST Ludo','GREEN'

.Net code
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Diagnostics;

public partial class CLR_Procedures
{
[Microsoft.SqlServer.Server.SqlProcedure]

}
    public static void SendStatusToWebservice(SqlString MyAppl, SqlString MyMessage, SqlString MyStatus)
    {
     string log;
        // Connect to webservice and add logging to it
        SQL_UDP.bgc.wss.Library wlib = new SQL_UDP.bgc.wss.Library();

        wlib.Credentials = System.Net.CredentialCache.DefaultCredentials;

        log = wlib.WSScreateLog(MyAppl.Value);
        wlib.WSSwriteLog(log, MyMessage.Value);// + " at @ " + DateTime.Now.ToString);
        wlib.SetBatchStatus(MyStatus.Value, log);
    }
};

Debug result:
Auto-attach to process '[3068] [SQL] bgc-mikmxeue486' on machine 'bgc-mikmxeue486' succeeded.
Debugging script from project script file.

The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Auto-attach to process '[3068] sqlservr.exe' on machine 'bgc-mikmxeue486' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'SQL_UDP', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'WebserviceCLR', Symbols loaded.
A .NET Framework error occurred during execution of user defined routine or aggregate 'CallWebservice':
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(CompilerParameter
...
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 WebserviceCLR.wsslib.Library...
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
A first chance exception of type 'System.InvalidOperationException' occurred in System.Xml.dll
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The program '[3068] [SQL] bgc-mikmxeue486: bgc-mikmxeue486' has exited with code 0 (0x0).
The program '[3068] sqlservr.exe: Managed' has exited with code 259 (0x103).