pamelia


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





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




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

Code Snippet

wsdl http://path_to_your_webservice.asmx


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.

Code Snippet

sgen your_assembly.dll /p


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




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




Re: soap message exchange between .NET Webservice and stored procedure

nielsb

See my reply on your other thread.

Niels




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..... Surprise(




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




Re: soap message exchange between .NET Webservice and stored procedure

pamelia


yes, i can...




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




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




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:

Code Snippet

string ret = hello.HelloWorld();
SqlContext.Pipe.Send(ret);


and see if that won't work.

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