The second procedure accepts a message from the request queue and halts processing for the indicated amount of time. First, the message is retrieved from the request queue. The RECEIVE statement is used for this. (Note that the semi-colon before the RETRIEVE is required unless the RECEIVE statement is the first statement in the batch.) The conversation handle, message body, and message type are retrieved from the queue and stored in variables for later processing. (Note that the message body is stored in an XML variable. This will be useful in a moment.) If a message was retrieved (@@ROWCOUNT > 0) then the delay parameter is obtained from the message document via a call to the VALUE method of the message body variable. The procedure then executes a WAITFOR statement using the provided delay parameter value. Next, the procedure sends a message to the response queue. In this example, the message data is identical to that received above. In practice, this would probably be different. Finally, because the request side of the conversation has completed its work, the END CONVERSATION statement is used. This statement informs Service Broker that this side of the conversation will send no more messages for this request.

The last stored procedure is very similar to the second. It obtains a message from the response queue and retrieves a delay value from this message. The WAITFOR statement is then used to execute a pause for the desired length. Finally, the END CONVERSATION statement is used to inform Service Broker that this side of the conversation is complete. Since both sides are now complete, the conversation ends.

Service Program Activation
At this point, the system would work perfectly well, but each stored procedure would have to be executed manually. This is usually not desirable. Instead, Service Broker provides a method for automatic procedure activation. Refer to listing 5. (Insert Listing 5) Here we see statements similar to those used to create our queues earlier. This time, however, the statements include an ACTIVATION clause. This clause specifies the name of a Service Program (a stored procedure) which should be executed whenever a message arrives at the queue. The MAX_QUEUE_READERS clause indicates the maximum number of separate instances of the stored procedure that can be executed simultaneously. Similarly, the MIN_QUEUE_READERS clause indicates the minimum number that can be used. (Use this setting with caution, as too large a value can hinder performance.) The EXECUTE AS clause specifies the user account under which the stored procedure should be run.

Summary
This small example demonstrates how Server Broker can be used to implement an asynchronous database process. It shows how messages can be sent back and forth between different queues, and how stored procedures can automatically be executed on an as-needed basis. Keep in mind that Service Broker is designed to be highly scalable, and can handle systems with many more queues, messages, and stored procedures than used here. In addition, X.509 certificates can be used to secure message data and to authenticate participating servers. Service Broker is a serious tool, and it deserves your consideration when you are designing your next system.