SQL Server Service Broker – Part 3

In Part I we looked at some general Service Broker concepts, and then in Part II we wrote some code to set up the Service Broker Objects.

In this Part of the Series (Part III), we’ll take a look at sending messages.

Service Broker uses a Conversation metaphor and we’ll see keywords like DIALOG and CONVERSATION that reflect this.

Having a real-life conversation is such a simple thing that we often don’t think about it, but essentially it goes something like

• Somebody starts the conversation.
• The other person responds.
• More responses happen
• The conversation comes to an end.

And this is effectively how Service Broker works – but it’s a bit more rigid and formal than its real-life counterpart.

So, we have all of our objects in place, the first thing we need to do is start the conversation.

USE [SB_Test];
GO

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [InitiatorService]
     TO SERVICE
      N'TargetService'
     ON CONTRACT
      [http://www.martincatherall.com.au/Contracts/ContractMain]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg =
       N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [http://www.martincatherall.com.au/MessageTypes/MessageOut]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

Notice that the TO SERVICE is quoted. That’s because usually the Target Service is on a remote machine and so the it’s an object the Initiator knows nothing about. In this example it’s all on the same machine for simplicity, but the same rules apply.

We’re also got ENCRYPTION switched off – again for simplicity, we’ll look at this more in another post.

We’ve also said BEGIN DIALOG but we could have said BEGIN DIALOG CONVERSATION – the CONVERSATION keyword is optional

We’ve then defined the message as a valid XML document and used the

SEND ON CONVERSATION

keywords to send the message.

We’ve then simply SELECTed the message – which is only done for demonstration purposes.

Note that this all happens in a single transaction.

After the code has completed the message should have been sent, so let’s check that.

Firstly, if the message has been sent, but there is a problem – such as the queue can’t be found – then the message will end up in the [sys].[transmission_queue] queue.

Every database has a [sys].[transmission_queue] queue, and this is a very good place to start troubleshooting.

SELECT * FROM [sys].[transmission_queue] [TQ];

The InitiatorQueue should be empty – but let’s check

SELECT * FROM [InitiatorQueue];

And we should see our single message sitting in the TargetQueue – again, let’s check

SELECT * FROM [TargetQueue];

Note that the BEGIN DIALOG command is sending the message between each SERVICE, and the SERVICE is ensuring that the message is placed on the respective QUEUE.

BEGIN DIALOG has an output parameter of type UNIQUEIDENTIFIER. This is used to uniquely identify our CONVERSATION and can be seen when looking at our QUEUE. This guid is known as the CONVERSATION_HANDLE

Now the Target SERVICE needs to respond.

--Start 
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

  RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM 
    TargetQueue

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'http://www.martincatherall.com.au/MessageTypes/MessageOut' -- Ensure this message type exists
  BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
 
     SEND ON CONVERSATION @RecvReqDlgHandle
        MESSAGE TYPE 
        [http://www.martincatherall.com.au/MessageTypes/MessageIn] -- Ensure this is a valid message
        (@ReplyMsg);
  
     END CONVERSATION @RecvReqDlgHandle;
  END
ELSE -- catch all Error handling (in case the message is NOT what was expected)
  BEGIN
    PRINT 'Invalid Message Type'
  END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

The response message follows a very similar pattern to the initial message.

However, we use a RECEIVE command in order to take messages from the QUEUE. Here, only the first message is being taken – using the TOP keyword.

First, we check that the message is of the type we require, then we simply formulate a new message – again as a valid XML document – and use the SEND ON CONVERSATION syntax to send the message back to the initiator.

We’ve also chosen to end the conversation at this point with the END CONVERSATION syntax.

Notice also that when we took the message from the Target QUEUE, that we also asked for the CONVERSATION_HANDLE and the MESSAGE TYPE

The message should now be on the Initiator queue – but let’s check all of the queues again.

SELECT * FROM [sys].[transmission_queue] [TQ];
SELECT * FROM [InitiatorQueue];
SELECT * FROM [TargetQueue];

But this time we should see 2 messages on the Initiator .

There are two message because we sent back a response (as XML) and an additional END CONVERSATION message to signal that the conversation is over.

Now all we need to do is grab this message from the initiator queue and acknowledge the END CONVERSATION message with another END CONVERSATION message, meaning that the CONVERSATION has been terminated from both sides and thus cleaned up correctly.

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;	

  RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM 
    InitiatorQueue

END CONVERSATION @RecvReplyDlgHandle;

SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

Again, note that this all happens in a single transaction.

We processed all of these messages ourselves – but really, we want to automate this process.

We’ll start looking at how to do that in Part IV of the series.