none
Internal Activation Stored Procedure Firing When Target OR Initiator Queues Populated (During Open Conversation)

    Question

  • I've come across a strange behaviour in T-SQL script I have written and which I can recreate with the Service Broker Tutorial at http://technet.microsoft.com/en-us/library/cc281528(v=sql.105).aspx where I have modified the code to dump Initiator and Target queue contents to a separate table (together with a timestamp and description of where in the code the dump occurs) so I can examine their contents at leisure. Code can be provided if needed..

    Discussing the tutorial (which still applies to my script as well):-

    1. Initiator begins dialog and sends a meassage. This populates the Target queue.

    2. Stored procedure fires and executes as a result of the Target queue being populated.

    3. Stored procedure execution - receives the message (Target queue now contains no rows).

    4. Stored procedure execution - determines correct message type and sends a message which populates the Initiator queue.

    5. The row is held in the Initiator Queue until we 'Receive' it.

    Everything is fine and is as expected. However, here is the wrap...

    At step 4, at exactly the same time the Initiator queue is populated, the stored procedure fires and executes again (with a different SPID). Don't forget, we haven't Received the message from the Initiator queue yet and is not as a result of intitiator 'ending conversation'. In fact, at this time the Target queue is empty and the check in the stored procedure for 0 row count when 'Receiving' from the queue prevents further messages being sent from the stored procedure.

    Is this normal behaviour that the stored procedure executes whenever EITHER queue is populated even though we have defined only the Target queue as the internal activator?

    Wednesday, June 25, 2014 10:58 AM

Answers

  • Thanks for the script.  The target queue activated proc is executed the second time, not because of the initiator queue message, but because a RECEIVE on the target queue has not yet returned zero rows and has not ended the conversation.  Once zero rows are received from the target queue, the activated proc won't be executed again until another message arrives.

    A common practice is to receive messages in a loop until the RECEIVE timeout is reached with no message received.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, June 27, 2014 12:45 PM

All replies

  • Is this normal behaviour that the stored procedure executes whenever EITHER queue is populated even though we have defined only the Target queue as the internal activator?

    No, I would expect the activated proc to fire only due to message on the target queue.  Can you share your script?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, June 26, 2014 2:13 AM
  • Is this normal behaviour that the stored procedure executes whenever EITHER queue is populated even though we have defined only the Target queue as the internal activator?

    No, I would expect the activated proc to fire only due to message on the target queue.  Can you share your script?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Hi Dan,

    The scripts are based on the tutorial at http://technet.microsoft.com/en-us/library/cc281511(v=sql.105).aspx

    As such, there are 4 scripts taken from the tutorail. I've modified them to add an extra table and to populate it at various stages in the script to dump the Initiator and Target queue contents. I've also commented out any statements concerning BEGIN/END TRAN etc for this exercise.

    I've also added a 5th script which simply queries the queue contents (with a Comments section which clears out the tables). When you highlight the first part of the script in 03 Begin Conversation and Transmit Message and execute it, observe the tables in 05 Query Tables and note the entry for ScriptLocation 'In proc after sending reply msg type' - there is, as expected a row in the Initiator queue, but immediately after this row is a 'Start of proc' entry - the stored procedure has fired again and has dumped only what is in the Initaiator queue, the Target queue is empty hence no row for it. The Initiator Queue has fired the stored procedure! You can then manually highlight the script in 03 which receives the msg in the Initiator queue and ends the dialog.

    This, as you may see from the USE statement, is run against SQL 2012. The same occurs in SQL 2008R2.

    01 Creating Base Conversation Objects.sql

    -- Enable Service Broker and switch to the AdventureWorks2008R2 database

    USE master;
    GO
    ALTER DATABASE AdventureWorks2012
          SET ENABLE_BROKER;
    GO
    USE AdventureWorks2012;
    GO

    -- create table to dump queue's to
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_Queues]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[_Queues](
     [QueueID] [int] IDENTITY(1,1) NOT NULL,
     [Timestamp] [datetime] NULL,
     [ScriptLocation] [varchar](255) NULL,
     [Queue] [varchar](9) NOT NULL,
     [status] [tinyint] NOT NULL,
     [priority] [tinyint] NULL,
     [queuing_order] [bigint] NOT NULL,
     [conversation_group_id] [uniqueidentifier] NOT NULL,
     [conversation_handle] [uniqueidentifier] NOT NULL,
     [message_sequence_number] [bigint] NOT NULL,
     [service_name] [sysname] NOT NULL,
     [service_id] [int] NOT NULL,
     [service_contract_name] [sysname] NOT NULL,
     [service_contract_id] [int] NOT NULL,
     [message_type_name] [sysname] NOT NULL,
     [message_type_id] [int] NOT NULL,
     [validation] [nchar](1) NOT NULL,
     [message_body] [varbinary](max) NULL,
     [message_enqueue_time] [datetime] NULL
    ) ON [PRIMARY]
    END
    GO

    -- Create the message types
    CREATE MESSAGE TYPE [//AWDB/InternalAct/RequestMessage]
     VALIDATION = WELL_FORMED_XML;
    CREATE MESSAGE TYPE [//AWDB/InternalAct/ReplyMessage]
     VALIDATION = WELL_FORMED_XML;
    GO

    -- Create the contract
    CREATE CONTRACT [//AWDB/InternalAct/SampleContract] (
     [//AWDB/InternalAct/RequestMessage] SENT BY INITIATOR,
     [//AWDB/InternalAct/ReplyMessage] SENT BY TARGET
    );
    GO

    -- Create the target queue and service
    CREATE QUEUE TargetQueueIntAct
     WITH STATUS = ON,
     RETENTION = OFF;

    CREATE SERVICE [//AWDB/InternalAct/TargetService]
     ON QUEUE TargetQueueIntAct ([//AWDB/InternalAct/SampleContract]);
    GO

    -- Create the initiator queue and service
    CREATE QUEUE InitiatorQueueIntAct
     WITH STATUS = ON,
     RETENTION = OFF;;

    CREATE SERVICE [//AWDB/InternalAct/InitiatorService]
     ON QUEUE InitiatorQueueIntAct ([//AWDB/InternalAct/SampleContract]);
    GO

    02 Creating internal activation procedure.sql

    USE AdventureWorks2012;
    GO

    -- Create an internal activation stored procedure

    CREATE PROCEDURE TargetActivProc
    AS
      DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
      DECLARE @RecvReqMsg NVARCHAR(100);
      DECLARE @RecvReqMsgName sysname;
      DECLARE @Timestamp DATETIME;
      DECLARE @ScriptLocation VARCHAR(255);
      DECLARE @RowCount INT;

      SELECT
     @Timestamp = GETDATE(),
     @ScriptLocation = 'Start of Proc'
     INSERT INTO dbo._Queues (
      [Timestamp],
      [ScriptLocation],
      [Queue],
      [status],
      [priority],
      [queuing_order],
      [conversation_group_id],
      [conversation_handle],
      [message_sequence_number],
      [service_name],
      [service_id],
      [service_contract_name],
      [service_contract_id],
      [message_type_name],
      [message_type_id],
      [validation],
      [message_body],
      [message_enqueue_time]
     )
     SELECT
      @Timestamp,
      @ScriptLocation,
      'Initiator' AS Queue,
      *
     FROM
      [dbo].[InitiatorQueueIntAct]
     INSERT INTO dbo._Queues (
      [Timestamp],
      [ScriptLocation],
      [Queue],
      [status],
      [priority],
      [queuing_order],
      [conversation_group_id],
      [conversation_handle],
      [message_sequence_number],
      [service_name],
      [service_id],
      [service_contract_name],
      [service_contract_id],
      [message_type_name],
      [message_type_id],
      [validation],
      [message_body],
      [message_enqueue_time]
     )
     SELECT
      @Timestamp,
      @ScriptLocation,
      'Target' AS Queue,
      *
     FROM
      [dbo].[TargetQueueIntAct]

      --WHILE (1=1)
      --BEGIN

        --BEGIN TRANSACTION;

     WAITFOR DELAY '00:00:10';

     SELECT
      @Timestamp = GETDATE(),
      @ScriptLocation = 'In proc prior to removing top row from target queue'
     INSERT INTO dbo._Queues (
      [Timestamp],
      [ScriptLocation],
      [Queue],
      [status],
      [priority],
      [queuing_order],
      [conversation_group_id],
      [conversation_handle],
      [message_sequence_number],
      [service_name],
      [service_id],
      [service_contract_name],
      [service_contract_id],
      [message_type_name],
      [message_type_id],
      [validation],
      [message_body],
      [message_enqueue_time]
     )
     SELECT
      @Timestamp,
      @ScriptLocation,
      'Initiator' AS Queue,
      *
     FROM
      [dbo].[InitiatorQueueIntAct]
     INSERT INTO dbo._Queues (
      [Timestamp],
      [ScriptLocation],
      [Queue],
      [status],
      [priority],
      [queuing_order],
      [conversation_group_id],
      [conversation_handle],
      [message_sequence_number],
      [service_name],
      [service_id],
      [service_contract_name],
      [service_contract_id],
      [message_type_name],
      [message_type_id],
      [validation],
      [message_body],
      [message_enqueue_time]
     )
     SELECT
      @Timestamp,
      @ScriptLocation,
      'Target' AS Queue,
      *
     FROM
      [dbo].[TargetQueueIntAct]

        WAITFOR
        ( RECEIVE TOP(1)
            @RecvReqDlgHandle = conversation_handle,
            @RecvReqMsg = message_body,
            @RecvReqMsgName = message_type_name
          FROM TargetQueueIntAct
        ), TIMEOUT 5000;

     SET @RowCount = @@ROWCOUNT;
     
        IF (@RowCount = 0) BEGIN
          --ROLLBACK TRANSACTION;
          --BREAK;
       GOTO Done
        END

        IF @RecvReqMsgName = N'//AWDB/InternalAct/RequestMessage' BEGIN
           DECLARE @ReplyMsg NVARCHAR(100);
           SELECT @ReplyMsg =
           N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

        WAITFOR DELAY '00:00:05';
     
           SEND ON CONVERSATION @RecvReqDlgHandle
       MESSAGE TYPE [//AWDB/InternalAct/ReplyMessage] (@ReplyMsg);

      SELECT
       @Timestamp = GETDATE(),
       @ScriptLocation = 'In proc after sending reply msg type'
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @Timestamp,
       @ScriptLocation,
       'Initiator' AS Queue,
       *
      FROM
       [dbo].[InitiatorQueueIntAct]
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @Timestamp,
       @ScriptLocation,
       'Target' AS Queue,
       *
      FROM
       [dbo].[TargetQueueIntAct]
        END
        IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
      
      SELECT
       @Timestamp = GETDATE(),
       @ScriptLocation = 'In proc prior ending conversation due to MsgType EndDialog'
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @Timestamp,
       @ScriptLocation,
       'Initiator' AS Queue,
       *
      FROM
       [dbo].[InitiatorQueueIntAct]
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @Timestamp,
       @ScriptLocation,
       'Target' AS Queue,
       *
      FROM
       [dbo].[TargetQueueIntAct]
          
        END CONVERSATION @RecvReqDlgHandle;

        END
        IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN
      
      SELECT
       @Timestamp = GETDATE(),
       @ScriptLocation = 'In proc prior to ending conversation due to MsgType Error'
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @TimeStamp,
       @ScriptLocation,
       'Initiator' AS Queue,
       *
      FROM
       [dbo].[InitiatorQueueIntAct]
      INSERT INTO dbo._Queues (
       [Timestamp],
       [ScriptLocation],
       [Queue],
       [status],
       [priority],
       [queuing_order],
       [conversation_group_id],
       [conversation_handle],
       [message_sequence_number],
       [service_name],
       [service_id],
       [service_contract_name],
       [service_contract_id],
       [message_type_name],
       [message_type_id],
       [validation],
       [message_body],
       [message_enqueue_time]
      )
      SELECT
       @Timestamp,
       @ScriptLocation,
       'Target' AS Queue,
       *
      FROM
       [dbo].[TargetQueueIntAct]

           END CONVERSATION @RecvReqDlgHandle;

        END
         
        --COMMIT TRANSACTION;

     Done:
      --END
    GO


    -- Alter the target queue to specify internal activation
    ALTER QUEUE TargetQueueIntAct
        WITH ACTIVATION
        ( STATUS = ON,
          PROCEDURE_NAME = TargetActivProc,
          MAX_QUEUE_READERS = 1,
          EXECUTE AS SELF
        );
    GO

    03 Begin conversation and Transmit Messages.sql

    USE AdventureWorks2012;
    GO

    -- Begin a conversation and send a request message
    DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
    DECLARE
     @RequestMsg NVARCHAR(100),
     @Timestamp DATETIME,
     @ScriptLocation VARCHAR(255);

    --BEGIN TRANSACTION;

    BEGIN DIALOG @InitDlgHandle
         FROM SERVICE
          [//AWDB/InternalAct/InitiatorService]
         TO SERVICE
          N'//AWDB/InternalAct/TargetService'
         ON CONTRACT
          [//AWDB/InternalAct/SampleContract]
    WITH
     ENCRYPTION = OFF;

    SELECT
     @Timestamp = GETDATE(),
     @ScriptLocation = 'After initiator BEGINs DIALOG'
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Initiator' AS Queue,
     *
    FROM
     [dbo].[InitiatorQueueIntAct]
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Target' AS Queue,
     *
    FROM
     [dbo].[TargetQueueIntAct]


    -- Send a message on the conversation
    SELECT @RequestMsg =
           N'<RequestMsg>Message for Target service.</RequestMsg>';

    SEND ON CONVERSATION @InitDlgHandle
         MESSAGE TYPE
         [//AWDB/InternalAct/RequestMessage]
         (@RequestMsg);

    SELECT
     @Timestamp = GETDATE(),
     @ScriptLocation = 'After initiator sends RequestMsg'
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Initiator' AS Queue,
     *
    FROM
     [dbo].[InitiatorQueueIntAct]
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Target' AS Queue,
     *
    FROM
     [dbo].[TargetQueueIntAct]

    -- Diplay sent request.
    SELECT @RequestMsg AS SentRequestMsg;

    --COMMIT TRANSACTION;
    GO


    -- ###  Receive the request and send a reply  ###
    -- When you send the request message, Service Broker automatically activates a copy of TargetActiveProc.
    -- The stored procedure receives the reply message from the TargetQueueIntAct and sends a reply message back to the initiator.

    -- ###  Receive the reply and end the conversation  ###
    -- The RECEIVE statement retrieves the reply message from the InitiatorQueueIntAct.
    -- The END CONVERSATION statement ends the initiator side of the conversation and sends an EndDialog message to the target service.
    -- The last SELECT statement displays the text of the reply message so that you can confirm it is the same as what was sent in the previous step.
    DECLARE @RecvReplyMsg NVARCHAR(100);
    DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER,
     @Timestamp DATETIME,
     @ScriptLocation VARCHAR(255);

    --BEGIN TRANSACTION;

    SELECT
     @Timestamp = GETDATE(),
     @ScriptLocation = 'Prior to initiator removeing a row from Initiator Queue'
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Initiator' AS Queue,
     *
    FROM
     [dbo].[InitiatorQueueIntAct]
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Target' AS Queue,
     *
    FROM
     [dbo].[TargetQueueIntAct]

    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReplyDlgHandle = conversation_handle,
        @RecvReplyMsg = message_body
        FROM InitiatorQueueIntAct
    ), TIMEOUT 5000;

    WAITFOR DELAY '00:00:10'

    END CONVERSATION @RecvReplyDlgHandle;

    SELECT
     @Timestamp = GETDATE(),
     @ScriptLocation = 'After initiator ends conversation'
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Initiator' AS Queue,
     *
    FROM
     [dbo].[InitiatorQueueIntAct]
    INSERT INTO dbo._Queues (
     [Timestamp],
     [ScriptLocation],
     [Queue],
     [status],
     [priority],
     [queuing_order],
     [conversation_group_id],
     [conversation_handle],
     [message_sequence_number],
     [service_name],
     [service_id],
     [service_contract_name],
     [service_contract_id],
     [message_type_name],
     [message_type_id],
     [validation],
     [message_body],
     [message_enqueue_time]
    )
    SELECT
     @TimeStamp,
     @ScriptLocation,
     'Target' AS Queue,
     *
    FROM
     [dbo].[TargetQueueIntAct]

    -- Display recieved request.
    SELECT @RecvReplyMsg AS ReceivedReplyMsg;

    --COMMIT TRANSACTION;
    GO

    -- ###  End the target side of the conversation  ###
    -- •When you run the END CONVERSATION statement for the initiator, Service Broker sends an EndDialog message to the TargetQueueIntAct queue.
    -- The TargetActiveProc procedure receives the EndDialog message and issues an END CONVERSATION that ends the target side of the conversation.

    04 Drop Conversation Objects.sql

    USE AdventureWorks2012;
    GO

    -- Drop the conversation objects
    IF EXISTS (SELECT * FROM sys.objects
               WHERE name =
               N'TargetActivProc')
         DROP PROCEDURE TargetActivProc;

    IF EXISTS (SELECT * FROM sys.services
               WHERE name =
               N'//AWDB/InternalAct/TargetService')
         DROP SERVICE
         [//AWDB/InternalAct/TargetService];

    IF EXISTS (SELECT * FROM sys.service_queues
               WHERE name = N'TargetQueueIntAct')
         DROP QUEUE TargetQueueIntAct;

    -- Drop the intitator queue and service if they already exist.
    IF EXISTS (SELECT * FROM sys.services
               WHERE name =
               N'//AWDB/InternalAct/InitiatorService')
         DROP SERVICE
         [//AWDB/InternalAct/InitiatorService];

    IF EXISTS (SELECT * FROM sys.service_queues
               WHERE name = N'InitiatorQueueIntAct')
         DROP QUEUE InitiatorQueueIntAct;

    -- Drop contract and message type if they already exist.
    IF EXISTS (SELECT * FROM sys.service_contracts
               WHERE name =
               N'//AWDB/InternalAct/SampleContract')
         DROP CONTRACT
         [//AWDB/InternalAct/SampleContract];

    IF EXISTS (SELECT * FROM sys.service_message_types
               WHERE name =
               N'//AWDB/InternalAct/RequestMessage')
         DROP MESSAGE TYPE
         [//AWDB/InternalAct/RequestMessage];

    IF EXISTS (SELECT * FROM sys.service_message_types
               WHERE name =
               N'//AWDB/InternalAct/ReplyMessage')
         DROP MESSAGE TYPE
         [//AWDB/InternalAct/ReplyMessage];

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_Queues]') AND type in (N'U'))
     DROP TABLE [dbo].[_Queues]
    GO

    05 Query Tables.sql

    USE Adventureworks2012;
    GO

    SELECT * FROM sys.conversation_endpoints
    SELECT * FROM sys.transmission_queue

    SELECT 'Initiator' AS Queue, * FROM [dbo].[InitiatorQueueIntAct]
    SELECT 'Target' AS Queue, * FROM [dbo].[TargetQueueIntAct]

    SELECT * FROM dbo._Queues ORDER BY QueueID

    /*
    TRUNCATE TABLE dbo._Queues


    DECLARE
     @Handle UNIQUEIDENTIFIER

    SET @Handle = (SELECT TOP 1 conversation_handle FROM sys.conversation_endpoints)

    IF @Handle IS NOT NULL BEGIN
     END CONVERSATION @Handle WITH CLEANUP
     SELECT * FROM sys.conversation_endpoints
     SELECT * FROM sys.transmission_queue
    END
    ELSE
     SELECT 'Nothing to clean up'
    */

    Thursday, June 26, 2014 7:50 AM
  • Thanks for the script.  The target queue activated proc is executed the second time, not because of the initiator queue message, but because a RECEIVE on the target queue has not yet returned zero rows and has not ended the conversation.  Once zero rows are received from the target queue, the activated proc won't be executed again until another message arrives.

    A common practice is to receive messages in a loop until the RECEIVE timeout is reached with no message received.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, June 27, 2014 12:45 PM