SQL Server with Mr. Denny

Jul 7 2008   11:00AM GMT

Back To Basics: The RECEIVE command (and END CONVERSATION)

Denny Cherry Denny Cherry Profile: Denny Cherry


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 @message_body XML
RECEIVE TOP (1) @handle=conversation_handle, @message_body=cast(message_body as XML)
FROM [QueueName]
END CONVERSATION @handleIt 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. 

I promise.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: