Sample Header Ad - 728x90

using WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused service broker keep calling activation function even there is no msg on the target queue

0 votes
0 answers
73 views
I have the following SQL service broker set up:
USE  master
-- Create database
ALTER DATABASE ServiceBrokerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ServiceBrokerDemo
CREATE DATABASE ServiceBrokerDemo;

-- Create a log
USE ServiceBrokerDemo
GO 
DROP TABLE IF EXISTS DemoServiceBrokerLog;
CREATE TABLE DemoServiceBrokerLog (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Date DATETIME,
	MessageTypeName sysname,
	ActivationFunction sysname, 
	MessageBody xml
);

-- CREATE TWO MESSAGE QUEUES: one for the sender and one for the receiver (who processes messages from the sender)
USE ServiceBrokerDemo
GO 

CREATE QUEUE dbo.InitiatorQueue WITH STATUS=ON ON [PRIMARY]
GO

CREATE QUEUE dbo.TargetQueue WITH STATUS=ON ON [PRIMARY]
GO


-- CREATE TWO ACTIVATION FUNCTIONS: for the two queues
-- This is for the target queue
CREATE OR ALTER PROCEDURE [dbo].[TargetQueueProcess]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @conversationHandle UNIQUEIDENTIFIER
	DECLARE @messagetypename NVARCHAR(256)
	DECLARE @messagebody XML

	DECLARE @errorNumber AS int;
	DECLARE @errorMessage AS nvarchar(3000);

	WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue)
	BEGIN
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messagetypename = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[TargetQueue]

		IF @conversationHandle IS NOT NULL
		BEGIN
			-- Log
			INSERT INTO DemoServiceBrokerLog VALUES (
						getdate(),
						@messageTypeName,
						'TargetQueueProcess', 
						@messagebody
				);


			IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation ' 
			BEGIN
				-- End the conversation 
				-- The initiator will receive an EndDialog message afterwards
				END CONVERSATION @conversationHandle;
			END
			ELSE IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/message ' 
			BEGIN
				BEGIN TRY 
					-- Process the message
					PRINT ('Do the actual task in here')
				END TRY 
				BEGIN CATCH 
					-- Capture the error
					SELECT @errorNumber = ERROR_NUMBER()
					, @errorMessage = ERROR_MESSAGE()

					-- End the conversation with an error
					-- The initiator will receive an Error message afterwards
					END CONVERSATION @conversationHandle WITH ERROR = @errorNumber DESCRIPTION = @errorMessage;
				END CATCH 
			END
		END
	END
END
GO

-- This procedure mainly to end the conversation at the initiator side and perform action upon errors
USE ServiceBrokerDemo
GO
CREATE OR ALTER PROCEDURE [dbo].[InitiatorQueueProcess]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @conversationHandle UNIQUEIDENTIFIER
	DECLARE @messageTypeName SYSNAME
	DECLARE @messagebody XML

	DECLARE @errorCode int;
	DECLARE @errorMessage nvarchar(3000);

	--BEGIN TRANSACTION
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messageTypeName = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[InitiatorQueue]

		IF @conversationHandle IS NOT NULL
		BEGIN
			INSERT INTO DemoServiceBrokerLog VALUES (
						getdate(),
						@messageTypeName,
						'InitiatorQueueProcess', 
						@messagebody
			)

			IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer ' 
			BEGIN
				;SEND ON CONVERSATION @conversationHandle 
					MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] ;
			END
			ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ' 
			BEGIN
				END CONVERSATION @conversationHandle;
			END
			ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error '
			BEGIN 
				-- Log the error before ending the conversation
				SET @errorCode = 
				(SELECT @messagebody.value(
				N'declare namespace 
				brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error "; 
				(/brokerns:Error/brokerns:Code)', 'int'));

				SET @errorMessage = (SELECT @messagebody.value(
				'declare namespace
				brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error ";
				(/brokerns:Error/brokerns:Description)', 'nvarchar(3000)'));

				-- do something with the error 
				PRINT('log the errors to some table')

				-- End conversation on the initiator side
				END CONVERSATION @conversationHandle;	
			END
		END
	--COMMIT TRANSACTION;
END
GO


-- Alter queue to associate them to the activation functions
USE ServiceBrokerDemo
GO
ALTER QUEUE dbo.TargetQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.TargetQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF) 
GO 
ALTER QUEUE dbo.InitiatorQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.InitiatorQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF) 
GO 

-- Create message type 
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message] 
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] 

-- Create contract
CREATE CONTRACT [http://my.dba.aventure/dbo/DemoServiceBroker/contract]  
(
	[http://my.dba.aventure/dbo/DemoServiceBroker/message]  SENT BY INITIATOR, 
	[http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation]  SENT BY INITIATOR
)

-- CREATE Service
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService]   ON QUEUE [dbo].[InitiatorQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract]) 
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/TargetService]   ON QUEUE [dbo].[TargetQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract]) 
I sent a message from the initiator to the target using the following code:
USE  ServiceBrokerDemo

DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @fromService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService '
DECLARE @toService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/TargetService '
DECLARE @contract SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/contract '

-- Craft a contrived message body
DECLARE @messageBody XML = 'some message'


BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE @fromService
TO SERVICE @toService
ON CONTRACT @contract
WITH ENCRYPTION = OFF;

BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 5

;SEND ON CONVERSATION @conversationHandle
	MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message]  
	(@messageBody)
I ran the following code to check the status of the queue
SELECT *
FROM sys.dm_broker_queue_monitors AS m
INNER JOIN sys.service_queues q
ON m.queue_id = q.object_id
The status of the queue keeps to be RECEIVES_OCCURRING even hours after the last time I ran the code that sends a message. I would expect the state to be INACTIVE since all the messages in the target already processed right? And the target activation function keeps executing even though nothing in the queue. However, as soon as I fixed the while loop part, the state of the queue is correctly changed to INACTIVE as soon the messages have been processed.
...
	WHILE (1=1)
	BEGIN
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messagetypename = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[TargetQueue]
		
		IF (@@ROWCOUNT=0)
		BEGIN
			BREAK
		END

		IF @conversationHandle IS NOT NULL
		BEGIN
...
Could you help me explain why the WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused the service broker keep calling the activation function to receive messages even though there is no message on the target queue?
Asked by Tuyen Nguyen (343 rep)
Oct 18, 2024, 06:20 PM
Last activity: Oct 30, 2024, 02:28 PM