SQL Server Service Broker Queue conversation dialog

Question

I've been working with Service Broker a few weeks now and while tracing a conversation, I discovered something odd... The conversation seems to have an additional message in queue at the end which also causes an error to occur.

From what I understand, the conversation dialog should go as follows:

1. Trigger fires and calls begin conversation
2. Initiator service places message on queue
3. Target service fires a stored proc, receives the message and ends the conversation
4. Initiator service fires a stored proc, receives the message and also ends the conversation.

What's happening is there's an extra message in the queue at the end which fires off the stored proc again, but the details (i.e. conversation_handle) is null. It also throws an error: Conversion failed when converting from a character string to uniqueidentifier. To get around the error, I cast the conversation_handle to varchar then check for null. Seems stupid to me that I would have to do this.

Update: The error has gone away - I believe it was happening when I was attempting to log the conversation_handle (which was null).

What's the proper way to end the conversation without getting the extra message at the end?

Here's what I have now:

alter proc dbo.MessageProcessor
as

begin

    set nocount on;
    set xact_abort on;

    declare @xactState smallint

    declare @handle uniqueidentifier, 
            @responseXml xml, 
            @messageType sysname;

    begin transaction;
    begin try

        ;receive top(1)
            @messageType = message_type_name, 
            @handle = conversation_handle, 
            @responseXml = message_body
        from dbo.MessageQueue

        if(@handle is not null)
        begin

            if (@messageType = N'DEFAULT')
            begin

                save transaction MessageProcessor_Tran

                begin try

                    -- doing work here

                end try
                begin catch

                    select @xactState = xact_state()

                    if(@xactState = -1)
                    begin
                        rollback;
                        raiserror(N'Unrecoverable error', 16, 1)
                    end
                    else if(@xactState = 1)
                    begin
                        rollback transaction MessageProcessor_tran
                    end

                    -- log error information

                end catch
            end 
            else if (@messageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
            begin
                declare @errorNumber int,
                        @errorMessage nvarchar(4000);

                with xmlnamespaces (DEFAULT N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
                select @errorNumber = @responseXml.value ('(/Error/Code)[1]', 'INT'),
                    @errorMessage = @responseXml.value ('(/Error/Description)[1]', 'NVARCHAR(4000)');
                -- log error
            end

            end conversation @handle
            set @handle = null
        end

        commit
    end try

    begin catch

        declare @error int, 
                @message nvarchar(2048)

        select @error = error_number(), 
            @message = error_message(), 
            @xactState = xact_state();

        if(@xactState <> 0)
            rollback;

        if(@handle is not null)
            end conversation @handle;

        -- log error            
        raiserror(N'Error: %i, %s', 1, 60, @error, @message) with log;

    end catch
end
go

Answer 1

Is not any extra message. Is just that your procedure is activated on an empty queue. Your activated procedure code should expect to be activated and RECEIVE to return an empty rowset every now and then (if you only test this with one message at a time it will happen every time, as you observed, under real load it will happen seldom).

In your code such a case (RECEIVE empty result set) would reflect in NULL @handle, NULL @messageType and NULL @responseXML, which is pretty much what you describe.