CODEWITHSUNDEEP
serviceservice-broker
user409679
user409679

Reputation: 81

Service Broker And Web services

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

Upvotes: 8

Views: 7470

Answers (2)

Sergey
Sergey

Reputation: 3213

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.

Upvotes: 4

Jānis
Jānis

Reputation: 2266

I would make windows service which is in the end of service broker (and call web service as in any win app). Somehow don't think calling web service from db is nice idea.

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

Upvotes: 0

Related Questions