Poison Message Sample in BOL

Hi,

We've been trying to diagnose a problem with service broker's poison message detection and tried to run the sample script in BOL to see how it solved the problem. It appears the example doesn't seem to work correctly,

          IF (@messageTypeName =
              '//Adventure-Works.com/AccountsPayable/ExpenseReport')
          BEGIN
            DECLARE @expenseReport NVARCHAR(MAX) ;
            SET @expenseReport = CAST(@messageBody AS NVARCHAR(MAX)) ;
            EXEC AdventureWorks.dbo.AddExpenseReport
              @report = @expenseReport ;
            IF @@ERROR <> 0
             BEGIN
               /* THIS ROLLBACK INCREMENTS THE FAILURE COUNT */
               ROLLBACK TRANSACTION UndoReceive ;
               EXEC TrackMessage @conversationHandle ;
             END ;
            ELSE
             BEGIN
               EXEC AdventureWorks.dbo.ClearMessageTracking
                 @conversationHandle ;
             END ;
           END ;
        ELSE

In the scenario where AddExpenseReport fails it will retry the message the next time round the loop - TrackMessage looks to see how many times the message has failed:

IF @count > 20
    /* @count WILL NEVER GET ABOVE 5 */
    BEGIN
      EXEC dbo.ClearMessageTracking @conversationHandle ;
      END CONVERSATION @conversationHandle
        WITH ERROR = 500
        DESCRIPTION = 'Unable to process message.' ;
    END ;
ELSE

But the counter will not get to 20 as after 5 the queue will be disabled which causes ProcessExpenseReport to fail. Subsequent message cannot then be processed until the queue is re-enabled.

We have changed the sample so that once the message has failed four times it is then written to a dead message table and we can then call commit so that the queue can carry on without being disabled:

[in ProcessExpenseReport]
             ...
            EXEC AdventureWorks.dbo.AddExpenseReport @report = @expenseReport ;
            IF @@error <> 0
             BEGIN
               -- The message has failed, we need to work out if we are safe to
               -- retry or not. If this message has failed four times already
               -- we need to recieve it, save it into another table and commit to
               -- avoid service broker from disabling the queue. Otherwise we can
               -- rollback and try again to see if it works next time.

               -- Find out how many times this message has failed
               declare @failCount int
               exec GetFailCount @conversationHandle, @failCount output

               -- If we are about to reach the threshold
               if @failCount >= 4
               begin
                  -- Insert the message into the dead message table. A sql agent
                  -- job will at some point in the future put the contents of this
                  -- table back onto the queue for reprocessing
                  EXEC DeadMessage @conversationHandle, @expenseReport
               end
               else
               begin
                  -- We are safe to rollback and take another run at it
                  ROLLBACK TRANSACTION UndoReceive ;
                  EXEC TrackMessage @conversationHandle ;
               end
             END ;
            ELSE
             BEGIN
               -- Call to SP worked we can clear down our message tracking
               EXEC ClearMessageTracking @conversationHandle ;
             END ;
           END ;
...

/* Procedure to find out how many times this message
   has failed to be processed */
CREATE PROCEDURE GetFailCount
   @conversationHandle uniqueidentifier,
   @failCount int output as
begin
   SELECT @failCount = [count] FROM ExpenseServiceFailedMessages
                                WHERE conversation_handle = @conversationHandle
end
go

/* Procedure to insert the message into the Dead message
   table and end the conversation */
create procedure DeadMessage
   @conversation_handle uniqueidentifier,
   @message_body varchar(max)
as
begin
   insert into ExpenseServiceDeadMessages ( message_body ) values ( @message_body )

   EXEC dbo.ClearMessageTracking @conversation_Handle ;
   END CONVERSATION @conversation_Handle
       WITH ERROR = 500
            DESCRIPTION = 'Unable to process message.' ;

end
go

/* Updated version of TrackMessage which no longer checks the
fail count - it just increments each time */
CREATE PROCEDURE TrackMessage
@conversationHandle uniqueidentifier
AS
BEGIN
IF @conversationHandle IS NULL
RETURN ;

DECLARE @count INT ;
SET @count = NULL ;
SET @count = (SELECT count FROM dbo.ExpenseServiceFailedMessages
                  WHERE conversation_handle = @conversationHandle) ;

  /* TrackMessage no longer checks the failure count */
IF @count IS NULL
    BEGIN
      INSERT INTO dbo.ExpenseServiceFailedMessages
        (count, conversation_handle)
        VALUES (1, @conversationHandle) ;
    END ;
ELSE
    BEGIN
      UPDATE dbo.ExpenseServiceFailedMessages
        SET count=count+1
        WHERE conversation_handle = @conversationHandle ;
    END ;
END ;
GO

/* New table to hold the Dead messages */
CREATE TABLE ExpenseServiceDeadMessages (
message_body varchar(max)
) ;

Now this solves the problem. If a message arrives that can't be processed (either becuase the message itself is bad, or because of an system fault with another system that we interact with) the message is moved to the DeadMessage table and the queue carries on and does not get disabled.

In our application however none of this code should be required, as long as we are confident that the data in the messages are "valid". In our case the only reason the processing would fail is if an external system we are dependant fails for some reason (e.g.  Maybe writing the message to disk and the disk is full ). In this scenario we want to retry the message indefinately until the error clears. If we could disable the poision message detection this would solve the problem and remove the need for this solution.

Any thoughts on this Have we missed the point somewhere....

Cheers,

Neil.

Answer this question