Export (0) Print
Expand All

Lesson 2: Creating an Internal Activation Procedure

In this lesson, you will learn to create a stored procedure to process messages from a Service Broker queue. You will also learn how to specify that the procedure be activated any time there are messages in the queue.

Switch to the AdventureWorks2008R2 database

  • Copy and paste the following code into a Query Editor window. Then, run it to switch context to the AdventureWorks2008R2 database.

    USE AdventureWorks2008R2;
    GO
    

Create an internal activation stored procedure

  • Copy and paste the following code into a Query Editor window. Then, run it to create a stored procedure. When it is run, the stored procedure keeps receiving messages as long as there are messages in the queue. If the receive times out without returning a message, the stored procedure ends. If the received message was a request message, the stored procedure returns a reply message. If the received message is an EndDialog message, the stored procedure ends the target side of the conversation. If the received message is and Error message, it rolls back the transaction.

    CREATE PROCEDURE TargetActivProc
    AS
      DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
      DECLARE @RecvReqMsg NVARCHAR(100);
      DECLARE @RecvReqMsgName sysname;
    
      WHILE (1=1)
      BEGIN
    
        BEGIN TRANSACTION;
    
        WAITFOR
        ( RECEIVE TOP(1)
            @RecvReqDlgHandle = conversation_handle,
            @RecvReqMsg = message_body,
            @RecvReqMsgName = message_type_name
          FROM TargetQueueIntAct
        ), TIMEOUT 5000;
    
        IF (@@ROWCOUNT = 0)
        BEGIN
          ROLLBACK TRANSACTION;
          BREAK;
        END
    
        IF @RecvReqMsgName =
           N'//AWDB/InternalAct/RequestMessage'
        BEGIN
           DECLARE @ReplyMsg NVARCHAR(100);
           SELECT @ReplyMsg =
           N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
     
           SEND ON CONVERSATION @RecvReqDlgHandle
                  MESSAGE TYPE 
                  [//AWDB/InternalAct/ReplyMessage]
                  (@ReplyMsg);
        END
        ELSE IF @RecvReqMsgName =
            N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        BEGIN
           END CONVERSATION @RecvReqDlgHandle;
        END
        ELSE IF @RecvReqMsgName =
            N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        BEGIN
           END CONVERSATION @RecvReqDlgHandle;
        END
          
        COMMIT TRANSACTION;
    
      END
    GO
    

Alter the target queue to specify internal activation

  • Copy and paste the following code into a Query Editor window. Then, run it to specify that Service Broker activate the TargetActiveProc stored procedure to process messages from TargetQueueIntAct. Service Broker will run a copy of TargetActiveProc any time a message is received in TargetQueueIntAct and no copy of the procedure is already running. Service Broker will run additional copies of TargetActiveProc whenever the existing copies do not keep up with the number of incoming messages.

    ALTER QUEUE TargetQueueIntAct
        WITH ACTIVATION
        ( STATUS = ON,
          PROCEDURE_NAME = TargetActivProc,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF
        );
    GO
    

You have successfully configured AdventureWorks2008R2 to support a conversation between the //AWDB/InternalAct/InitiatorService and the //AWDB/InternalAct/TargetService. Next, you will complete a conversation using the configuration. See Lesson 3: Beginning a Conversation and Transmitting Messages.

Community Additions

ADD
Show:
© 2014 Microsoft