Chris Johnson continues a series on Service Broker:
This stored procedure is used by the queue to handle messages that arrive. When a message arrives the queue will execute the procedure over and over until there are no more messages on the queue. Your stored procedure therefore needs to be removing messages as it goes. You have the option to have multiple versions of the query executing at once, to clear down a queue faster or to keep up with a high volume of messages, using the
MAX_QUEUE_READERS setting. You can turn the stored procedure on or off using the
STATUS, while this is set to
OFF nothing will happen but as soon as it is set to
ON the query will start processing messages again. Finally you need to specify what user the query will execute under. The options here are
SELF, as the current user (the person who runs the
OWNER, as the person who owns the queue, or a username that the current user has impersonate permissions for.
Chris provides us an example of what a procedure could look like and also covers briefly the concept of poison message handling.