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 M
y
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 M
y
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 M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\Syst
em.Transactions.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
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 M
y
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 M
y
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\Sy
stem.Web.Services.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\S
ystem.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.C
ompileAssemblyFromSourceBatch(CompilerPa
rameters options, String[] sources)
at
System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerPa
rameter
..
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)."examnotes" <Ludo@.discussions.microsoft.com> wrote in
news:825F081A-11D6-4D36-8C9A-F8B4AEA96CE5@.microsoft.com:
> 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.
>
[snip]
> 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:
As the error says, SQLCLR doesn't allow you to load a dynamically
generated assembly (which happens when you do web-services). You need to
sgen the proxy code into a dll and catalogue that assembly in SQL
Server.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********
Steven Hemingray - MSFT wrote: