Share via

Activating stored procedures asynchronously

  • Article

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

GO

CREATE PROCEDURE HelloWorldResponder

AS

BEGIN

      DECLARE @conversationHandle UNIQUEIDENTIFIER

      DECLARE @message_body NVARCHAR(MAX)

      DECLARE @message_type_name SYSNAME

      WHILE (1 = 1)

      BEGIN

            BEGIN TRANSACTION

            -- Wait for 5 seconds for messages to arrive

            WAITFOR (

                  -- For simplicity we process one message at a time

                  RECEIVE TOP(1)

                   @message_type_name=message_type_name,

                  @conversationHandle=conversation_handle,

                  @message_body=message_body

                  FROM [HelloWorldTargetQueue]), TIMEOUT 100

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

            IF (@@rowcount <= 0)

                  BREAK;

            -- If this is a hello world message,

            -- respond with an appropriate greeting

            IF @message_type_name = N'HelloWorldRequest'

            BEGIN

                  SEND ON CONVERSATION @conversationHandle

                        MESSAGE TYPE [HelloWorldResponse]

                        (N'Hello From '+@@servername )

                  END CONVERSATION @conversationHandle

            END

            COMMIT

      END

      COMMIT

END

GO

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

      ALTER QUEUE [HelloWorldTargetQueue] WITH

      ACTIVATION (

            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 TRANSACTION

-- Begin a dialog to the Hello World Service

BEGIN DIALOG @conversationHandle

    FROM SERVICE [HelloWorldResponseService]

    TO SERVICE 'HelloWorldRequestService'

    ON CONTRACT [HelloWorldContract]

    WITH ENCRYPTION = OFF, LIFETIME = 600;

-- Send message

SEND ON CONVERSATION @conversationHandle

   MESSAGE TYPE [HelloWorldRequest] (N'Hello World')

 

COMMIT

 

 

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

 

            RECEIVE

   message_type_name, 

   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.

Comments