Posted by: Denny Cherry
Back To Basics, RECEIVE, Service Broker, SQL, T/SQL
The RECEIVE command is the last of the Service Broker items to cover in the Back To Basics series. The RECEIVE command is what is used to actually get the message out of the queue so that you can process the data within the message_body column. The RECEIVE command works just like the SELECT statement for the most part. It has a FROM clause and a WHERE clause (which most people don’t use, but it’s there if you need it). After you receive the message you will usually want to run the END CONVERSATION command. This will tell the remote machine that you have receive the message and that no more messages should be sent on this conversation. I usually only send a single message per conversation, so I don’t put in any logic to see if this is the last message in the conversation.
The basic syntax is very straight forward.
DECLARE @handle UNIQUEIDENTIFIER
DECLARE @message_body XML
RECEIVE TOP (1) @handle=conversation_handle, @message_body=cast(message_body as XML)
END CONVERSATION @handle
It is required that you cast the message body to XML, as it's transmitted as binary data. (If you are sending a single value or a binary blob then change the cast as needed.) You don't have to write a second command to delete the message from the queue after you receive it. The RECEIVE command handles the SELECT and DELETE in a single command.
If you haven't been able to make to to one of my sessions, here is the sample source code which I use for the service broker session. This ZIP file contains not only the sample code, but also the slide deck from the presentation which covers much of which I've covered in these blogs. If you have any questions about the sample code please feel free to post those questions here so that everyone can see the answers.
I may take a little break from the Back To Basics posts for a little bit, but I haven’t really decided yet, so if you see more popping up on the site, I guess that means that I’ve decided to keep working the series for a bit. If I do take a break, I will start back up shortly.