My Adventures in Coding

October 15, 2007

Service Broker Tutorial – SQL Server 2005

Filed under: SQL Server 2005 — Brian @ 5:46 pm
Tags: ,

The following is meant as a “Quick Start” tutorial for anyone needing to create a service broker queue under extreme time pressure! This is a generalized example of a db script for a new use of service broker queue called fooQueue. Replace attributes foo1, foo2 with whatever attributes (and types) you need in your message. Replace foo with a relevant name, e.g. CustomerOrders.  Replace schemaName with dbo or with whatever schema name you are using.  (I have experienced tricky permissions issues in the past with schema names other than dbo.)

NOTE: During our use of service broker, we ran into a number of “Gotchas”. The bonus of these examples is all of those fixes are built into the example. So if you need a high performance service broker queue where all of the problems have been ironed out, then feel free to cut and past this example.

STEP 1: Create the message schema

This schema defines the XML messages that can be placed onto the service broker queue.

CREATE XML SCHEMA COLLECTION [schemaName].[fooMessageXmlSchema] 
AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <xsd:complexType name="foo">
            <xsd:attribute name="foo1" type="xsd:integer" />
            <xsd:attribute name="foo2" type="xsd:integer" />
      </xsd:complexType>
      <xsd:complexType name="Root">
            <xsd:sequence>
                 <xsd:element name="foo" type="foo" minOccurs="0" maxOccurs="unbounded"/>
            </xsd:sequence>
      </xsd:complexType>
      <xsd:element name="Root" type="Root" />
</xsd:schema>'

STEP 2: Create the Send Request Stored Procedure

This procedure is responsible for taking a properly formatted message and sending it to the service broker queue.

CREATE PROCEDURE [schemaName].[fooSend]
      @body XML (DOCUMENT fooMessageXmlSchema)
AS
      DECLARE @handle uniqueidentifier;

      IF (convert(varchar, @body.query('count(/Root/*)')) != 0)
      BEGIN
            BEGIN DIALOG CONVERSATION     @handle
            FROM SERVICE      fooService
            TO SERVICE  'fooService'
            ON CONTRACT fooMessageContract
            WITH ENCRYPTION = OFF;        SEND
            ON CONVERSATION   @handle
            MESSAGE TYPE      fooMessage (@body);
            END CONVERSATION @handle;
      END
GO

STEP 3: Send Stored Procedure Caller

This piece of code creates a new message that can then be placed on the queue. Place this code inside some calling stored procedure and/or trigger.

DECLARE @body XML (DOCUMENT fooMessageXmlSchema);
      SELECT @body =  (
           SELECT (
                SELECT
                     i.foo1 as <span>"@foo1"</span>,
                     i.foo2 as <span>"@foo2"</span>
                FROM inserted i
                FOR XML PATH ('foo'), TYPE
           ) FOR XML PATH ('Root'), TYPE
     );    EXEC schemaName.fooSend @body;

STEP 4: Create the Receive Stored Procedure

The receive stored procedure is where the bulk of your work will be done. This is the sproc that will be called by the service broker queue every time a new message is inserted into the queue.

CREATE PROCEDURE [schemaName].[fooReceive]
AS
SET NOCOUNT ON;
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @mt SYSNAME;
DECLARE @MessageBody XML (DOCUMENT fooMessageXmlSchema);
DECLARE @foo1 int;
DECLARE @foo2 int;
DECLARE @fooCount int;
DECLARE @fooCurrent int;
BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
      @mt = message_type_name,
      @MessageBody = message_body
      FROM [schemaName].[fooQueue]), TIMEOUT 1000;
WHILE (@dh IS NOT NULL)
BEGIN
      BEGIN TRY
            IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
		END CONVERSATION @dh;
            END
            ELSE IF (@mt = N'fooMessage')
            BEGIN
                  SET @fooCount = @MessageBody.value('count(/Root/foo)', 'int');
                  SET @fooCurrent = 1;

                  WHILE (@fooCurrent &lt;= @fooCount)
                  BEGIN
                        --set variable values from xml message body
                        SELECT @foo1 = Root.foo.value('@foo1','int'),
                               @foo2 = Root.foo.value('@foo2','int')
                        FROM @MessageBody.nodes('(/Root/foo[sql:variable(&quot;@fooCurrent&quot;)])') AS Root(foo)

                        -- DO YOUR WORK HERE

                        -- increment the position

                        SET @fooCurrent = @fooCurrent + 1;
                  END
                  END CONVERSATION @dh;
            END
            COMMIT;
      END TRY
      BEGIN CATCH
                  -- If there are any exceptions then end conversation,
                  -- rollback the transaction and stop processing the queue
                  END CONVERSATION @dh;
                  ROLLBACK TRANSACTION;
                  INSERT INTO ServiceBrokerErrors (queuename, errornumber, errormessage, errordate)
                  VALUES ('fooQueue', ERROR_NUMBER(), ERROR_MESSAGE(), getdate())
                  ALTER QUEUE [schemaName].[fooQueue] WITH ACTIVATION ( STATUS = OFF );
                  BEGIN TRANSACTION
      END CATCH   -- Try to loop once more if there are more messages
      SELECT @dh = NULL;
      BEGIN TRANSACTION;
      WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
            @mt = message_type_name,
            @MessageBody = message_body
            FROM [schemaName].[fooQueue]), TIMEOUT 1000;
END
COMMIT;
GO

Note: The RECEIVE top (1) used in the above store procedure is good for a single reader. If you need to use multiple readers, you can use the following to receive all messages for a conversation group:

DECLARE  @Messages TABLE (MessageBody XML, MessageTypeName SYSNAME, ddh UNIQUEIDENTIFIER)
WAITFOR (RECEIVE  message_body, message_type_name, conversation_handle
	FROM [dbo].[EmailServiceColpaEventQueue]
	INTO @Messages), TIMEOUT 1000

You can then loop through the messages in the temporary table and end the conversation appropriately.

STEP 5: Setting up the Service Broker Queue

Create the Message Type for the queue. This states what the queue will accept as a valid message. In this Message Type we are saying it must be an XML document and must conform to the schema fooMessageXmlSchema.

CREATE MESSAGE TYPE [fooMessage] AUTHORIZATION [dbo] VALIDATION = VALID_XML
WITH SCHEMA COLLECTION [schemaName].[fooMessageXmlSchema]
GO

The Contract just states who is authorized to add messages to the queue and references the Message Type.

CREATE CONTRACT [fooMessageContract]
AUTHORIZATION [dbo] ([fooMessage] SENT BY INITIATOR)
GO

Create the new service broker queue.
Note: When creating the service broker queue, the Activation section allows you to attach the receive stored procedure (e.g., fooReceive) that you have already created. This stored procedure will be called any time a new message is inserted into the queue.

CREATE QUEUE [schemaName].[fooQueue]
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION 
(
      STATUS = ON ,
      PROCEDURE_NAME = [schemaName].[fooReceive] ,
      MAX_QUEUE_READERS = 1 ,
      EXECUTE AS OWNER
)
ON [PRIMARY]
GO

Finally, create a new Service to run the new queue.

CREATE SERVICE [fooService]
AUTHORIZATION [dbo]  ON QUEUE [schemaName].[fooQueue] ([fooMessageContract])
GO

Well that is all! I hope this helps!

Advertisements

2 Comments »

  1. Thank you for your great tutorial. It saved me a whole lot of time and frustration.

    Comment by Yigal — May 28, 2013 @ 3:33 pm | Reply

  2. I was wondering, if you use the “Send Stored Procedure Caller” in a trigger, then you will have one XML body per statement. Suppose you do an update like this “UPDATE profiles SET first_name = UPPER(first_name);” and the profiles table contains 3.000.000 records, wouldn’t that be a huge XML you create and try to store in the queue? Wouldn’t it be better to have one XML per record (instead of per statement) and send each record to the queue?

    Comment by Freek — June 12, 2013 @ 9:27 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: