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:
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
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.