service - 服务代理和Web服务

Question

我想实现一个调用Web服务的存储过程(在Service Broker基础设施内)。我看了Aschenbrenner关于Service Broker的书中的一些示例。但是，我找不到任何带有Web服务的电话。有人可以帮忙吗？

谢谢
Sqlbs

Answer 1

我们公司也有类似的任务，并且找到了一个最佳的解决方案是使用带有外部激活器的异步触发器，该激活器从.NET调用Web服务，并在成功调用后使消息出队。这意味着您创建了一个常规数据库触发器，该触发器将消息发送到服务代理队列以进行异步处理。 AKA异步触发。这是克劳斯书第10章的范例

-- 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);

与其使用会通过托管代码(内部激活)调用Web服务的存储过程，我们决定最好将处理卸载到sql server之外。并找到了微软创建的这个漂亮的小工具- External Activator
当队列中有新消息时，它将监听激活队列并启动应用程序。有关实现的信息，请参阅本书中的克劳斯第4章。