Geek Answers Handbook

Service Broker and Web Services

I want to implement a stored procedure (as part of an infrastructure brokerage service) that calls a web service. I looked at a few examples from Aschenbrenner’s book on Service Broker. However, I am not with a web service call. Can anyone help?

Thanks Sqlbs

+6
service service-broker
source share
3 answers

We had a similar task in my company, and we decided that the best solution was to use asynchronous triggers with an external activator that calls web services from .NET and de-queues messages after a successful call. What this means is that you create a regular database trigger that sends a message to the broker services queue for asynchronous processing. AKA Asynchronous Trigger. here is a sample from chapter 10 of Klaus’s book

-- Create the trigger written with T-SQL CREATE TRIGGER OnCustomerInserted ON Customers FOR INSERT AS DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @fromService SYSNAME DECLARE @toService SYSNAME DECLARE @onContract SYSNAME DECLARE @messageBody XML SET @fromService = 'CustomerInsertedClient' SET @toService = 'CustomerInsertedService' SET @onContract = 'http://ssb.csharp.at/SSB_Book/c10/CustomerInsertContract' -- Check if there is already an ongoing conversation with the TargetService SELECT @conversationHandle = ConversationHandle FROM SessionConversations WHERE SPID = @@SPID AND FromService = @fromService AND ToService = @toService AND OnContract = @onContract IF @conversationHandle IS NULL BEGIN -- We have to begin a new Service Broker conversation with the TargetService BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE @fromService TO SERVICE @toService ON CONTRACT @onContract WITH ENCRYPTION = OFF; -- Create the dialog timer for ending the ongoing conversation BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 5; -- Store the ongoing conversation for further use INSERT INTO SessionConversations (SPID, FromService, ToService, OnContract, ConversationHandle) VALUES ( @@SPID, @fromService, @toService, @onContract, @conversationHandle ) END -- Construct the request message SET @messageBody = (SELECT * FROM INSERTED FOR XML AUTO, ELEMENTS); -- Send the message to the TargetService ;SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c10/CustomerInsertedRequestMessage] (@messageBody);

Instead of using stored procedures that would invoke web services through managed code (internal activation), we decided that it was better to offload this processing outside the sql server. And I found this beautiful little tool created by Microsoft - an external activator that will listen to the activation queue and launch the application when a new message appears in the queue. For implementation, please refer to Chapter 4 of Klaus in the book.

+4
source share

See the first example in chapter 10. If your question is about the details of making web service calls, mark the question with the appropriate web service tags, not the service broker.

0
source share

I would make a Windows service, which is located at the end of the service broker (and call the web service as in any win application). Somehow do not think that calling a web service from db is a good idea.

can find the external activator here . and download the service broker interface / external activator here . The service broker interface is just great! easy to use.

0
source share

More articles:


All Articles