Service Broker And Web services

Question

I want to implement a stored procedure (within a service broker infrasturture) which calls a web service. I looked some examples from Aschenbrenner's book on Service Broker. However I don't find any with a web service call. Could anyone help?

Thanks Sqlbs

Answer 1

We had a similar task at my company and figured out an optimal solution was to use asynchronous triggers with external activator which calls webservices from .NET and de-queues messages after successful call. What that meas is that you create a regular database trigger that sends a message to the service broker queue for asynchronous processing. AKA Asynchronous trigger. here is a sample from the chapter 10 of Klause'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 which would call web services through managed code (internal activation) we decided that it's better to offload that processing outside of sql server. And found this nice little tool created by Microsoft - External Activator that will listen to the activation queue and launch an application when there is a new message in the queue. For implementation please refer to Klaus's Chapter 4 in the book.