Activating stored procedures asynchronously

I was tempted to post a HelloWorld sample for Service Broker, but since Roger Wolter’s article already provides that, I decided to avoid the redundancy. Instead I shall describe how to setup async execution using Service Broker’s activation mechanism. Even if you think you’d never want to send messages in a database, I highly recommend copy-pasting the code from the link above into your SQL Server 2005 Beta 2 to see how ‘cool’ this really is.

Service Broker enables the DBA to configure a service to start a program whenever there is work to be done. This activation mechanism can either be internal or external. Internal activation is controlled by the system and allows the DBA to specify a stored procedure to invoke whenever there are messages in the queue to be consumed. External activation works by notifying (sending a message to) a configured service which may be implemented by the developer.

When internal activation is enabled on a queue, Service Broker creates a queue monitor to start the associated stored procedure whenever required. The queue monitor checks the status of the queue periodically and it is also triggered by several events such as:

·         A new message arriving on the queue

·         RECEIVE statement executed for the queue

·         A transaction containing a RECEIVE rolls back

·         All stored procedures started by the queue monitor exit

·         ALTER statement executed for the queue

If the queue readers cannot keep up with the rate of incoming messages, the internal activator will schedule more readers concurrently (up to the maximum limit) for parallel execution.

Let us modify the HelloWorld sample to use internal activation. First we create a stored procedure that acts as our service program:

USE HelloWorldDB



CREATE PROCEDURE HelloWorldResponder



      DECLARE @conversationHandle UNIQUEIDENTIFIER

      DECLARE @message_body NVARCHAR(MAX)

      DECLARE @message_type_name SYSNAME

      WHILE (1 = 1)



            — Wait for 5 seconds for messages to arrive

            WAITFOR (

                  — For simplicity we process one message at a time

                  RECEIVE TOP(1)    




                  FROM [HelloWorldTargetQueue]), TIMEOUT 100

            — If a message was received, process it, else skip

            IF (@@rowcount <= 0)


            — If this is a hello world message,

            — respond with an appropriate greeting

            IF @message_type_name = N‘HelloWorldRequest’


                  SEND ON CONVERSATION @conversationHandle

                        MESSAGE TYPE [HelloWorldResponse]

                        (N‘Hello From ‘+@@servername )

                  END CONVERSATION @conversationHandle







Next we ALTER the target queue to setup internal activation with status ‘on’:

      ALTER QUEUE [HelloWorldTargetQueue] WITH


            STATUS = ON, — Turn on internal activation

            PROCEDURE_NAME = [HelloWorldResponder], — Our stored proc

            MAX_QUEUE_READERS = 4, — Up to 4 concurrent readers

            EXECUTE AS SELF)  Execute as user of incoming dialog


Now try to send a message again using the script in the HelloWorld sample:

DECLARE @conversationHandle uniqueidentifier



— Begin a dialog to the Hello World Service


BEGIN DIALOG  @conversationHandle

    FROM SERVICE    [HelloWorldResponseService]

    TO SERVICE      ‘HelloWorldRequestService’

    ON CONTRACT     [HelloWorldContract]



— Send message

SEND ON CONVERSATION @conversationHandle

   MESSAGE TYPE [HelloWorldRequest] (N‘Hello World’)





You should have received the reply and the ‘end dialog’ message on the initiator queue:




   cast(message_body as nvarchar(MAX))

   FROM [HelloWorldInitiatorQueue]


As an exercise try to create a service program that accepts a stock symbol in the request message, looks up the price from a table and returns that as the reply. If you are successful, leave a comment on this post.