Moving Messages in Service Broker


  • Hi. Please excuse my complete newbie question, but I am just trying to get my head around the whole business of moving messages from one queue to another. I have inherited an application and the person responsible for developing it has now left the company.

    I have about 40,000 messages in a single SB queue and -some- (about 80%) of those messages are taking about a minute each to be processed. I want to move these messages (which are not really that important, but must be processed eventually) into a second queue so that the remaining 20% of messages (that are much higher priority for the business) can be processed. It's only a one-time move.

    What do I do? If this was SQL I would transactionally delete from one table and insert into another. Job done. I have generated the SQL required to get  a list of the 'conversation_handle's that need to be moved, I just need to understand how to pick up a message (or group of messages if this is possible) from one queue and drop it unchanged into another inside a single transaction.

    Thanks in advance.

    Friday, February 03, 2012 3:21 PM

All replies

  • Hi JonRussell,

    I would like to involve someone familiar with this issue to have a look, and give an update as soon as possible.

    Stephanie Lv

    TechNet Community Support

    Monday, February 06, 2012 6:16 AM
  • How do you process the data in th queue?

    Assuming you're using internal activation (calling a sproc on queue activation) you could modify this sproc to redirect the "less important" information to a separate queue and immediately process the "important ones". This new queue would then need to call a separate sproc that would perform the processing. It might help to set activation status = off for this new queue until all original messages are processed.

    Those are the steps I'd use:

    1) set activation status = off for the current queue

    2) write a new sproc that would only process the "time consuming messages"

    3) write a new sproc that would only process the "fast messages"

    4) create a new queue as a target for the "time consuming messages" with activation procedure_name="time consuming messages" and also create a new service for that queue.

    5) write a "wrapper-sproc" that would call the "fast message sproc"  or send the data to the new queue depending on the criteria you alread identified

    6) change the sproc in the current queue activation to the new wrapper sproc.

    Sunday, February 12, 2012 4:50 PM
  • Hi JonRussell,

    Since you already have the SQL to identify the conversation handles for the messages that you need to move to another Queue, the task is quite simple. Assuming that you have already created the new Queue, the following code can be used the model a working solution for you. This sample code will run in a loop and fetch one message out of the old Queue ( this deleting it from the olde Queue) and insert it into the new Queue. It will keep looping until no more messages are found in the old Queue that match the conversation handles that you have identified ; I have use a While Loop here, but you can use any kind of loop that you may be comfortable with. This is just a sample code that I have stubbed out, its not compiled or tested. Please test thoroughly before you adapt it for use in your enviornment.

    -- Declare Variables to store old message count, message_body, old & new conversation handles
    DECLARE @OldMsgCnt INT,
    @Msg_Body XML,

    -- initialize old message count
    SET @OldMsgCnt = 1;

    -- while loop condition set to break loop when old message count comes down to Zero
    WHILE @OldMsgCnt <> 0
    BEGIN -- Begin While Loop

    -- fetch relevant details of old conversation into variables for top 1 message
    @OldCH = conversation_handle ,
    @Msg_Body = CAST (message_body AS XML)
    -- instead of conversation handles, you could also try to use the condition you used to identify the messages to be moved
    conversation_handle IN ('List of conversation_handles of messages identified to be moved');

    -- proceed only if old conversation handle is found
        -- begin a new dialog and store the new conversation handle
           FROM SERVICE new_initiator_service
           TO SERVICE 'new_target_service'
           ON CONTRACT new_services_contract
           ENCRYPTION = OFF;
    -- send message on the new conversation handle , this puts the message on the new Queue       
    MESSAGE TYPE new_message_type (@Msg_Body);

    -- you can optionally log these messages into a staging table before you END CONVERSATION

    -- end the old conversation, this will take the message OFF of the old queue

    PRINT N'1 Message moved to new Queue';
    PRINT N'No Messages found in Old Queue'

    -- set the old queue count
    SELECT @OldMsgCnt = COUNT(*) 
    conversation_handle IN ('List of conversation_handles of messages identified to be moved');

    END -- End While Loop

    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    • Proposed as answer by Sanil Mhatre Monday, February 13, 2012 6:44 PM
    Monday, February 13, 2012 6:36 PM