Hello,
is message exchange between a .NET Webservice and a SQL stored procedure possible
And if, could you please explain me how Or give me a tip where i can get more informations
and maybe samples
thanks for your help
regards
pamelia
Re: soap message exchange between .NET Webservice and stored procedure
nielsb
I don't fully understand the question, but in SQL 2005 you can use a CLR based stored proc to call into a .NET web-service.
Niels
I don't fully understand the question, but in SQL 2005 you can use a CLR based stored proc to call into a .NET web-service.
Niels
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
hi,
i would like to send informations from a CLR stored proc to a .NET web-service and get some back.
ist it possible
pamelia
Re: soap message exchange between .NET Webservice and stored procedure
nielsb
So, in other words; you would like to call a web-service from a clr based proc If so, yes you can.
Niels
So, in other words; you would like to call a web-service from a clr based proc If so, yes you can.
Niels
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
Thanks for your help!
But how can i call a web-service from a clr based proc
Do you know where i can get an example
pamelia
Re: soap message exchange between .NET Webservice and stored procedure
nielsb
You call it like you would call it from a "normal" clr method (with one exception).
1. Create your web-service
2. Generate proxy-code against your web-service
3. Include the proxy-code in your CLR proc project
4. Write your CLR proc(s) against the proxy code.
5. Compile your CLR proc assembly
Here is now the difference/exception from if this had been "normal" CLR code. When executing against a web-service, the XmlSerializer will generate and load a dynamic assembly based on the proxy-code. Dynamic assembly loading is not allowed from inside SQLCLR. Therefore you have to pre-generate the assembly that would be dynamically created and loaded by using the sgen tool.
6. Run sgen against your compiled assembly from step 6 (assuming your proxy code is part of that assembly) with the /p switch.
7. Now you can deploy the two assemblies (your SQLCLR assembly and the one generated from sgen) to the database. You have to depoy them with a PERMISSION_SET of EXTERNAL_ACCESS.
8. After having deployed the assemblies you can now create the usual T-SQL wrapper methods against your CLR code.
9. Execute!!!!
Hope this helps!
Niels
You call it like you would call it from a "normal" clr method (with one exception).
1. Create your web-service
2. Generate proxy-code against your web-service
Code Snippet
wsdl http://path_to_your_webservice.asmx
4. Write your CLR proc(s) against the proxy code.
5. Compile your CLR proc assembly
Here is now the difference/exception from if this had been "normal" CLR code. When executing against a web-service, the XmlSerializer will generate and load a dynamic assembly based on the proxy-code. Dynamic assembly loading is not allowed from inside SQLCLR. Therefore you have to pre-generate the assembly that would be dynamically created and loaded by using the sgen tool.
6. Run sgen against your compiled assembly from step 6 (assuming your proxy code is part of that assembly) with the /p switch.
Code Snippet
sgen your_assembly.dll /p
8. After having deployed the assemblies you can now create the usual T-SQL wrapper methods against your CLR code.
9. Execute!!!!
Hope this helps!
Niels
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
Hi,
i still can't deploy my assemblies to the database!! Now i get the following error message:
CREATE ASSEMBLY for assembly '16082007' failed because assembly '16082007' is not authorized for
PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true:
the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY
database property on; or the assembly is signed with a certificate or an asymmetric key that has
a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached
this database, make sure the database owner is mapped to the correct login on this server. If not,
use sp_changedbowner to fix the problem.
What do i have to do to get this authorization
Hi,
i still can't deploy my assemblies to the database!! Now i get the following error message:
CREATE ASSEMBLY for assembly '16082007' failed because assembly '16082007' is not authorized for
PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true:
the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY
database property on; or the assembly is signed with a certificate or an asymmetric key that has
a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached
this database, make sure the database owner is mapped to the correct login on this server. If not,
use sp_changedbowner to fix the problem.
What do i have to do to get this authorization
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
Hi Niels,
thanks for your help, now i can deploy my assemblies into my database!
and i allready created my stored procedure, and of course it doesn't works ;o)
i get the error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "WebAsmproc":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException:
No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket,
IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Service1.HelloWorld()
at StoredProcedures.WebService()
and now.....
(
Hi Niels,
thanks for your help, now i can deploy my assemblies into my database!
and i allready created my stored procedure, and of course it doesn't works ;o)
i get the error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "WebAsmproc":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException:
No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket,
IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Service1.HelloWorld()
at StoredProcedures.WebService()
and now.....
(Re: soap message exchange between .NET Webservice and stored procedure
nielsb
Hmm, can you execute the web-method from a console-app, on the same machine your SQL Server is I.e can you create a console application, and use your proxy code and execute
Niels
Hmm, can you execute the web-method from a console-app, on the same machine your SQL Server is I.e can you create a console application, and use your proxy code and execute
Niels
Re: soap message exchange between .NET Webservice and stored procedure
nielsb
Can you please post the code that you are using in the CLR proc, and the code from the console app - please.
Niels
Can you please post the code that you are using in the CLR proc, and the code from the console app - please.
Niels
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
i just started with a simple "Hello World" app to look if it works.
the console app:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
public static void Main()
{
Service1 hello = new Service1();
Console.WriteLine(hello.HelloWorld());
Console.ReadLine();
}
};
the clr proc:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebService()
{
Service1 hello = new Service1();
SqlContext.Pipe.Send(hello.HelloWorld());
}
};
the console app works fine, but i still get the error message with the clr sproc.
any idea
i just started with a simple "Hello World" app to look if it works.
the console app:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
public static void Main()
{
Service1 hello = new Service1();
Console.WriteLine(hello.HelloWorld());
Console.ReadLine();
}
};
the clr proc:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebService()
{
Service1 hello = new Service1();
SqlContext.Pipe.Send(hello.HelloWorld());
}
};
the console app works fine, but i still get the error message with the clr sproc.
any idea
Re: soap message exchange between .NET Webservice and stored procedure
nielsb
Ah, I assume that your HelloWorld web-method returns a string
In that case change your proc code to:
and see if that won't work.
Niels
Ah, I assume that your HelloWorld web-method returns a string
In that case change your proc code to:
Code Snippet
string ret = hello.HelloWorld();
SqlContext.Pipe.Send(ret);
Niels
Re: soap message exchange between .NET Webservice and stored procedure
pamelia
sorry, but it still doesn't work!
unable to connect to the remote server,
because the target machine actively refused it.
is there maybe anything else i have to regulate for getting connected to
a web-service from a clr stored procedure
sorry, but it still doesn't work!
unable to connect to the remote server,
because the target machine actively refused it.
is there maybe anything else i have to regulate for getting connected to
a web-service from a clr stored procedure