In our estate we've set up Service Broker to perfom an action:
- Based on a schedule a query is executed and its results are sent, via service broker, to the other service on a remote server
- 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
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 thenN'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 rightsys.conversation_endpoints. When the target ends the conversation, theEndDialogmessage 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.