none
Calling out to a WCF Service from a Sql Server CLR Stored procedure

    Question

  •  

    Is it possible to call a WCF service from a SQL Server CLR Stored Procedure.

    We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way.  We may want to alter the WCF configuration depending on where the service is sitting, same box or remote.

    Please do not respond with 'why do you want to do this'  the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.

    Cheers,

    Darren

    Thursday, March 22, 2007 1:47 PM

Answers

  • Darren,

    WCF supports both imperative and configuration style. Imperative meaning you can control things right from your code and in configuration mode you can specify properties or setting in a XML config file. So you can choose which model you want to go with. For your case, you just need to stick with imperative style on your client side. There are various approaches you could take to create your client.

    1. Use svcutil.exe to generate one for you.
    2. Write your own client code using WCF.

    Please look at [1], [2] msdn articles to understand how you can use svcutil to generate client code for you. Once it has generated this client, you can create a proxy similar to following code,

    // Create a binding instance. This should match with what your Server Endpoint expects
    BasicHttpBinding binding = new BasicHttpBinding();

    // Create an EndpointAddress where your server is listening on.
    EndpointAddress endpointAddress = new EndpointAddress(new Uri("
    http://<Server>/<ServiceEndpoint>"));

    // Create a proxy.
    CalculatorProxy proxy = new CalculatorProxy(endpointAddress, binding);

    // Consume a proxy.
    proxy.Operation();

    If you want to write your own WCF client look at msdn sample [3]. You will need to use ChannelFactory or DuplexChannelFactory depending on your requirement.

    References:

    [1] http://msdn2.microsoft.com/en-us/library/ms733133.aspx
    [2] http://msdn2.microsoft.com/en-us/library/ms734691.aspx
    [3] http://msdn2.microsoft.com/en-us/library/ms734681.aspx

    Thanks,

    Thursday, March 22, 2007 8:33 PM
    Moderator

All replies

  • Darren,

    WCF supports both imperative and configuration style. Imperative meaning you can control things right from your code and in configuration mode you can specify properties or setting in a XML config file. So you can choose which model you want to go with. For your case, you just need to stick with imperative style on your client side. There are various approaches you could take to create your client.

    1. Use svcutil.exe to generate one for you.
    2. Write your own client code using WCF.

    Please look at [1], [2] msdn articles to understand how you can use svcutil to generate client code for you. Once it has generated this client, you can create a proxy similar to following code,

    // Create a binding instance. This should match with what your Server Endpoint expects
    BasicHttpBinding binding = new BasicHttpBinding();

    // Create an EndpointAddress where your server is listening on.
    EndpointAddress endpointAddress = new EndpointAddress(new Uri("
    http://<Server>/<ServiceEndpoint>"));

    // Create a proxy.
    CalculatorProxy proxy = new CalculatorProxy(endpointAddress, binding);

    // Consume a proxy.
    proxy.Operation();

    If you want to write your own WCF client look at msdn sample [3]. You will need to use ChannelFactory or DuplexChannelFactory depending on your requirement.

    References:

    [1] http://msdn2.microsoft.com/en-us/library/ms733133.aspx
    [2] http://msdn2.microsoft.com/en-us/library/ms734691.aspx
    [3] http://msdn2.microsoft.com/en-us/library/ms734681.aspx

    Thanks,

    Thursday, March 22, 2007 8:33 PM
    Moderator
  • OK thanks

    I think thats given me enough of a head start.

    Cheers,

    Darren

     

     

    Friday, March 23, 2007 3:15 PM
  • Hello;

    Sorry for reviving this topic, but i'm dealing with this situation and i can't get it right!

    I've had VS2005 add the service references for me (generating the app.config. the references .map and .cs files), but whenever i do a normal reference call, i get:

    System.InvalidOperationException:Could not find default endpoint element that references contract '<contract name>' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.

    if i attempt a solution like the one in this topic, with the endpoint being the one provided me by IIS

    EndpointAddress endpoint = new EndpointAddress(new Uri("<address>.svc"));

    i get

    System.ServiceModel.EndpointNotFoundException:There was no endpoint listening at <service url>.svc that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. : System.Net.WebException: The remote server returned an error: (404) Not Found.
    at System.Net.HttpWebRequest.GetResponse()
    at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)

    I believe that the app.config where the endpoint address is located is not deployed into the assembly and thus unaccesible form the CLR-SP.

    However, I have an regular Win32 app that calls the services properly in IIS, but this SQL-CLR solution doesn't, so the problem isnt about the services themselves, i presume.

    What am i doing wrong? I see some solutions proposing the usage of svcutil, but VS is supposed to do that for me when I do "Add Service Reference", right?

    Thanks for any help you can provide!

    Pedro Almeida

    Friday, November 23, 2007 1:36 PM
  • I've dabbled with this approach for some time but opted against it for a few reasons:

     

    1. Reliability: You will need to code against WCF errors, otherwise the CLR stored proc may crash affecting the stability of SQL Server.

     

    2. Deployment: Depending on your work place, you may even find that the DBA will refuse to deploy your stored proc! This is especially true in investment banks.

     

    3. Performance: Calling into an external process from a CLR stored proc via WCF can degrade performance, especially, if there were lots of WCF calls from the CLR stored proc.

     

    4. Coding: As far as I know, there is no easy way to add a reference to System.ServiceModel in a SQL CLR project. You will need to generate proxies manually.

     

    The solution that worked for me was to monitor incoming messages into the SQL Server Service Broker via a dedicated .NET server-side process (search for my previous posts under my name). Once my server received a Service Broker notification, it immediately read the Service Broker message, and then used WCF to send the message on to external systems. I found this approach to resolve all the problems above.

     

    Hope this helps.

     

    Friday, November 23, 2007 5:00 PM
  • Hello, Yazan;

    This stored procedure is actually the result of a SQL Service Broker reading. A trigger sends messages to the broker that has this SP associated with the receiving queue. This SP itself (in managed code) would then call the appropriate external WCF service.
    which seems to be what you adopted to gracefully avoid the need to generate proxies manually. but am i missing something there? 
    i just add the Service Reference and then call it like i do in my Win32 app... 

    Thanks for your reply!
    Friday, November 23, 2007 5:28 PM
  • Tried a different thing, to manually setup the entire contents of the app.config file (since it doesnt seem its loaded):

    BasicHttpBinding binding = new BasicHttpBinding();
    binding.Name = "Test";
    binding.CloseTimeout = TimeSpan.Parse("00:01:00");
    binding.OpenTimeout = TimeSpan.Parse("00:01:00");
    binding.ReceiveTimeout = TimeSpan.Parse("00:10:00");
    binding.SendTimeout= TimeSpan.Parse("00:10:00");
    binding.BypassProxyOnLocal = false;
    binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
    binding.MaxBufferPoolSize = 524288;
    binding.MaxReceivedMessageSize = 65536;
    binding.MessageEncoding = WSMessageEncoding.Text;
    binding.TextEncoding = Encoding.UTF8;
    binding.UseDefaultWebProxy = true;
    binding.AllowCookies = false;

    binding.ReaderQuotas.MaxDepth = 32;
    binding.ReaderQuotas.MaxStringContentLength = 8192;
    binding.ReaderQuotas.MaxArrayLength = 16384;
    binding.ReaderQuotas.MaxBytesPerRead = 4096;
    binding.ReaderQuotas.MaxNameTableCharCount = 16384;

    // Create an EndpointAddress where your server is listening on.
    EndpointAddress endpointAddress = new EndpointAddress(new Uri("http://192.168.0.163/Folder/service.svc"));

    // Create a proxy.
    MyClient aaa = new MyClient(binding, endpointAddress);

    // Consume a proxy.
    string response = aaa.Method(inputString);


    Which gave me the following error:

    System.ServiceModel.EndpointNotFoundException
    Could not connect to http://192.168.0.163/Folder/service.svc. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
    System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
    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)
    --- End of inner exception stack trace ---
    at System.Net.HttpWebRequest.GetRequestStream()
    at System.ServiceModel.Channels.HttpOutput.WebRequestHttpOutput.GetOutputStream()


    I'm clueless...

    Thanks for any help you can provide.


    *** EDIT: ***
    seems like my IP had changed by that time.
    i'm getting this error again:

    System.ServiceModel.EndpointNotFoundException:There was no endpoint listening at http://192.168.0.156/Folder/service.svc that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. : System.Net.WebException: The remote server returned an error: (404) Not Found.
    at System.Net.HttpWebRequest.GetResponse()
    at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)

    *** EDIT 2: ***
    Getting closer...

    System.ServiceModel.ProtocolException:Content Type text/xml; charset=utf-8 was not supported by service <service>. The client and service bindings may be mismatched. : System.Net.WebException: The remote server returned an error: (415) Cannot process the message because the content type 'text/xml; charset=utf-8' was not the expected type 'application/soap+xml; charset=utf-8'..
    at System.Net.HttpWebRequest.GetResponse()
    at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)

    *** EDIT 3: ***
    fixed...
    changed the
    BasicHttpBinding binding = new BasicHttpBinding(); //(SOAP 1.1)
    to
    WSHttpBinding binding = new WSHttpBinding(); //(SOAP 1.2)

    and changed the service endpoint address to one that doesnt work when i click it, but does when i use it. dont know why...
    Monday, November 26, 2007 12:50 PM