Setting up a Message Queue in SQL Server

By on in , with No Comments

Over the years I’ve had a few instances where I’ve had to set up a message queue with the broker in SQL Server and every time I come back to set up another queue I find the documentation to be very confusing. So here is my attempt to make the setup easier to follow. Please take note this has been simplified from the documentation that you can find here.

Setting up the Message Queues.

To begin with you need to make sure that the database that your working with has the broker enabled.

ALTER DATABASE databasename
	SET ENABLE_BROKER;
GO

Now both message types need to be created the requesting message and the response message. The requesting message is the one that initiates the communication and the response message is the response to the request.

CREATE MESSAGE TYPE [RequestMessage] VALIDATION = WELL_FORMED_XML
 
CREATE MESSAGE TYPE [ResponseMessage] VALIDATION = WELL_FORMED_XML
 
GO

Now we need to tell SQL Server about the contract between the messages, which basically tells the server which is the requesting message and which is the response message.

CREATE CONTRACT [Contract]
      ([RequestMessage]
       SENT BY INITIATOR,
       [ResponseMessage]
       SENT BY TARGET
      );
GO

After the contract we need to create a queue and service for each message that will be transmitted. Here is the code for the request message.

CREATE QUEUE RequestQueue;
 
CREATE SERVICE
       [RequestService]
       ON QUEUE RequestQueue;
GO

Then we create the queue for the response message and make sure it’s connected to the contract.

CREATE QUEUE ResponseQueue;
 
CREATE SERVICE
       [ResponseService]
       ON QUEUE ResponseQueue(Contract);
GO

Setting up the stored procedures to handle the communication.

After everything is setup we need to create a stored procedure that will send the request message to the queue. When using this, remember that the request message needs to be valid xml.

CREATE PROCEDURE  [dbo].[SendRequestMessage] 
	@RequestMsg XML
AS
BEGIN
	SET NOCOUNT ON;	
	DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	DECLARE @TranName VARCHAR(20);
	SELECT @TranName = 'SendRequest';
 
	BEGIN TRANSACTION @TranName;
		BEGIN TRY
			BEGIN DIALOG @InitDlgHandle
				FROM SERVICE
				[RequestService]
				TO SERVICE
				N'ResponseService'
			 ON CONTRACT
				[Contract]
			 WITH
				 ENCRYPTION = OFF;
 
 
			SEND ON CONVERSATION @InitDlgHandle
				MESSAGE TYPE 
				[RequestMessage]
                                (@RequestMsg);
		
			SELECT @InitDlgHandle AS DialogHandle;						
 
		END TRY
	BEGIN CATCH	
		IF @@TRANCOUNT > 0
		BEGIN
			SELECT 
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE();
				
			ROLLBACK TRANSACTION @TranName;
			
			RAISERROR (@ErrorMessage, 
               @ErrorSeverity, 
               @ErrorState 
               );			
		END
	END CATCH;
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION @TranName
	
END

Now we need to setup a stored procedure that will receive the request message and send a response back to whoever sent the request.

CREATE PROCEDURE  [dbo].[ReceiveRequestMessage] 	
AS
BEGIN
	SET NOCOUNT ON;	
	DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
	DECLARE @RecvReqMsg XML;
	DECLARE @RecvReqMsgName sysname;
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	DECLARE @TranName VARCHAR(22);
	SELECT @TranName = 'ReceiveRequest';
 
	BEGIN TRANSACTION @TranName;
		BEGIN TRY
			WAITFOR
                        ( RECEIVE TOP(1)
				@RecvReqDlgHandle = conversation_handle,
				@RecvReqMsg = message_body,
				@RecvReqMsgName = message_type_name
				FROM RuleSetTargetQueue
			), TIMEOUT 1000;
 
			IF @RecvReqMsgName = N'RunRequestMessage'
			BEGIN
				-- In this area we can put in any processing that needs to occur before we send back the response.
			
				DECLARE @ReplyMsg NVARCHAR(100);
				SELECT @ReplyMsg =
						N'1Message received on ' + CAST(GETDATE() as nvarchar(12)) + '';
 
				SEND ON CONVERSATION @RecvReqDlgHandle
					MESSAGE TYPE 
					[ResponseMessage]
                                        (@ReplyMsg);
 
				END CONVERSATION @RecvReqDlgHandle;

                                -- Make sure to select any information that you need to return to the caller of the stored procedure.
			END
			
		END TRY
	BEGIN CATCH	
		IF @@TRANCOUNT > 0
		BEGIN
			SELECT 
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE();
				
			ROLLBACK TRANSACTION @TranName;
			
			RAISERROR (@ErrorMessage, 
               @ErrorSeverity, 
               @ErrorState 
               );			
		END
	END CATCH;
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION @TranName
	
END

And for the very last part, we need to receive the response message.

CREATE PROCEDURE  [dbo].[ReceiveResponseMessage] 	
AS
BEGIN
	SET NOCOUNT ON;	
	DECLARE @RecvReplyMsg NVARCHAR(100);
	DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	DECLARE @TranName VARCHAR(23);
	SELECT @TranName = 'ReceiveResponse';
 
	BEGIN TRANSACTION @TranName;
		BEGIN TRY
			WAITFOR
                        ( RECEIVE TOP(1)
				@RecvReplyDlgHandle = conversation_handle,
				@RecvReplyMsg = message_body
				FROM RuleSetInitiatorQueue
			), TIMEOUT 8000;
 
			IF @RecvReplyDlgHandle is not null    -- Only end the conversation if we received a reply.
			BEGIN  
				END CONVERSATION @RecvReplyDlgHandle;
			END
 
			SELECT @RecvReplyMsg AS ReceivedReplyMsg;
 
		END TRY
	BEGIN CATCH	
		IF @@TRANCOUNT > 0
		BEGIN
			SELECT 
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE();
				
			ROLLBACK TRANSACTION @TranName;
			
			RAISERROR (@ErrorMessage, 
               @ErrorSeverity, 
               @ErrorState 
               );			
		END
	END CATCH;
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION @TranName
	
END 
« »