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 theSTATUS
, while this is set toOFF
nothing will happen but as soon as it is set toON
the query will start processing messages again. Finally you need to specify what user the query will execute under. The options here areSELF
, as the current user (the person who runs theCREATE
orALTER
script),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.
Comments closed