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.