SQL Server with Mr. Denny

Jun 5 2008   11:00AM GMT

Back To Basics: Service Broker Queues

Denny Cherry Denny Cherry Profile: Denny Cherry

The service broker queue is where the messages are stored after they are delivered, but before they are processed.  They are kind of like tables as you insert data into them (sending the message) and you view the data in them (receiving the message).  While tables can have triggers, queues can have what are called activated procedures.  If a queue has an activated procedure defined when the message arrives in the queue the queue runs the procedure.  No data is passed to the procedure, that procedure still needs to do a receive against the procedure (if that’s what actually needs to be done).

Setting up an activation procedure is a bit tricky as you have to have the queue to create the procedure, and you have to have the procedure to set it as an activation procedure.  So what I like to do is create the queue, then the procedure, then alter the queue to setup the activation.  This way I can send messages to the queue right away without worrying about getting errors because the procedure doesn’t exist.

Unlike tables, you do not get to define the structure of the queue.  All data is stored within the message_body field, so if you need to send more than one value it’s recommend that you use an XML document to send the data.  The syntax to create the queue is very simply.

CREATE QUEUE [QueueName]

Because the queue does hold the messages you can define what file group you would like the data within the queue to be stored in.  If you have a business need to keep the messages around after processing you can specify the RETENTION keyword setting it to ON.  By default queues are enabled when they are created.  If you do not want to allow messages to be delivered to the queue specify the STATUS keyword setting it to OFF.

Denny

 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: