2

In our estate we've set up Service Broker to perfom an action:

  1. Based on a schedule a query is executed and its results are sent, via service broker, to the other service on a remote server
  2. Message is received and consumed by activation procedure on target server.

The issue is that for some reason the message is consumed multiple times on target server, despite the fact that it has been sent just once.

I'm fairly new to Service Broker and am seeking for advice/guidance of what could be the exact problem and what could be the cause of it.

The code of the procedure that begins the dialog:

CREATE PROCEDURE [ServiceBroker].[SendMessage]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @Message XML
            , @ConversationHandle UNIQUEIDENTIFIER
            , @Counter INT = 1
            , @Error INT
            , @TRANCOUNT INT = @@TRANCOUNT
            , @SavePoint CHAR(32) = REPLACE(NEWID(), '-', '')
            , @FromService sysname = 'SimpleServiceInitiator_' + DB_NAME();

        SET @Message = (
            SELECT 1 AS Value
            FOR XML PATH('Message'), ELEMENTS XSINIL, TYPE
            );

        BEGIN TRANSACTION;
        SAVE TRANSACTION @SavePoint;

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;

            BEGIN DIALOG @ConversationHandle
            FROM SERVICE @FromService
            TO SERVICE 'SimpleServiceTarget'
            ON CONTRACT SimpleContract
            WITH ENCRYPTION = OFF;

            -- Set an two minute timer on the conversation
            BEGIN CONVERSATION TIMER (@ConversationHandle) TIMEOUT = 120;

            -- Attempt to SEND on the associated conversation
            SEND
                ON CONVERSATION @ConversationHandle
                MESSAGE TYPE SimpleMessage (@Message);

            -- Successful send, just exit the loop
            SET @Error = @@ERROR;
            IF @Error = 0 BREAK;

            SET @Counter += 1;
            IF @Counter > 3
            BEGIN
                -- We failed 3 times in a row, something must be broken
                RAISERROR (N'Failed to SEND on a conversation for more than 3 times. Error %i.', 16, 1, @Error);
                BREAK;
            END;
        END;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() = 1
        BEGIN
            -- Rollback any active or uncommittable transactions before inserting information in the ErrorLog.
            -- We can still save the other activities in the transaction.
            ROLLBACK TRANSACTION @SavePoint;
            COMMIT TRANSACTION;
        END;
        ELSE IF XACT_STATE() = -1 AND @TRANCOUNT = 0
        BEGIN
            -- If the tran is doomed, and the @TRANCOUNT was 0, we have to do a full roll back
            ROLLBACK TRANSACTION;
        END;
        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO

And that of the activation SP on target:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @IsRecognised BIT = 0
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                -- SELECT *
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = N'EndOfStream'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            ELSE IF @MessageType = N'SimpleMessage'
            BEGIN
                SET @IsRecognised = 1;

                /* DO -TSQL MAGIC*/

                END CONVERSATION @ConversationHandle;
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        IF @IsRecognised = 1
        BEGIN
            -- 6a) Commit otherwise end the conversation to get the message off the queue **
            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRANSACTION;
            END;
            ELSE
            BEGIN
                SET @ErrorCode = 127;
                SET @ErrorDescription = N'Unable to process message';

                END CONVERSATION @ConversationHandle
                    WITH ERROR = @ErrorCode
                    DESCRIPTION = @ErrorDescription;
            END;
        END;
        ELSE IF XACT_STATE() IN (-1, 1) AND @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;

        INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
        VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
    END CATCH;
END;

This is activation SP on initiator that's supposed to end conversation:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue_Initiator]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        END CONVERSATION @ConversationHandle
            WITH ERROR = @ErrorCode
            DESCRIPTION = @ErrorDescription;

        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO
  • I suggest you run an XE trace of sp_statement_completed events filtered on the activated proc. I was able to run your code successfully with a local database instance Not related to your issue but how are the EndDialog messages sent back to the initiator consumed? A fire-and-forget pattern will leak conversation handles. Not sure what scenario the send retry is addressing but, IMHO, it adds unneeded complexity. – Dan Guzman Mar 12 '17 at 13:11
  • @DanGuzman I'm doing that in target activation SP, no? ELSE IF @MessageType = N'SimpleMessage' BEGIN SET @IsRecognised = 1; /* DO -TSQL MAGIC*/ END CONVERSATION @ConversationHandle; END; I consume message on receiver, do t-sql nd end conversation. I assume that then N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' message is being sent to initiator and conversation should be closed. I was following this pattern to set broker up: davewentzel.com/content/… Talking about retry, I should remove it. You're right – Evaldas Buinauskas Mar 12 '17 at 13:28
  • A SB conversation is a dialog with 2 endpoints as you can see from sys.conversation_endpoints. When the target ends the conversation, the EndDialog message which is written to the initiator queue. An initiator service process needs to receive initiator queue messages and close that side of the dialog when an EndDialog or Error message is received. That can be done with an initiator queue activated proc. – Dan Guzman Mar 12 '17 at 13:58
  • @DanGuzman Sorry I've forgot to add it here in the code. I have set it up on initiator. Will update question accordingly. – Evaldas Buinauskas Mar 12 '17 at 14:01
  • I don't think the initiator message code relevant for your problem but wanted to make sure you weren't leaking handles. The trace results should provide the info to troubleshoot the issue.. – Dan Guzman Mar 12 '17 at 14:05
1

Dan Guzman gave me some good tips and after further research I've found out that my routes were configured incorrectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.