locked
Queue Activation Procedure runs twice for each message RRS feed

  • Question

  • Hi, I"m by no means a Service Broker expert.   I've setup a full service broker and now I see that everytime a message comes into the target queue, the activation procedure tied to it runs twice!!! I have a small update table  statement set there to see how many times the procedure executes on a message and I see that my update updates the table column values twice (I've setup something simple like Update tableX set col1 = col1 + 1).  So everytime a new message comes, the value gets incremented twice!  Any idea on what's going on? 

    And I'm sure the first time the message is retrieved from the queue, the queue gets empty and the proc runs once.  Not sure why it runs again, without a message in the queue! 

     

    Here's the activation proc content (Srry guys, for some reason insert code, is having problems !):

    DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
    
    DECLARE @RecvReqMsg xml;
    
    DECLARE @RecvReqMsgName sysname;
    
    
    
    
    
    WAITFOR
    
    ( RECEIVE TOP(1)
    
     @RecvReqDlgHandle = conversation_handle,
    
     @RecvReqMsg = message_body,
    
     @RecvReqMsgName = message_type_name
    
     FROM InstTargetQueue
    
    ), TIMEOUT 1000;
    
    
    
    --SELECT @RecvReqMsg AS ReceivedRequestMsg;
    
    
    
    --SELECT @RecvReqMsg.query('/MailChimp/Users')
    
    
    
     SELECT N.x.value('(./userid/text())[1]', 'int') AS userid FROM		@RecvReqMsg.nodes('/MailChimp/Users') AS N(x)
    
     
    
     UPDATE SB_TEST1 SET col1 = col1+1
    
    
    Here's the create queue code:
    CREATE QUEUE [dbo].[InstTargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SB_UpdateUserPrefs] , MAX_QUEUE_READERS = 5 , EXECUTE AS OWNER ) ON [PRIMARY] 
    GO
    Tuesday, April 20, 2010 12:09 AM

Answers

  • The database engine doesn't guarantee that there are some messages waiting to be received whenever an activated procedure is executed. Therefore you should code it to handle the case where RECEIVE returns an empty result set. Another good idea would be to have a loop that keeps receiving messages until the RECEIVE statement returns an empty result set. See here for an example of a properly coded activated procedure.
    • Proposed as answer by Pawel Marciniak Wednesday, April 21, 2010 4:14 PM
    • Marked as answer by SQLSQL2018 Wednesday, May 12, 2010 8:50 PM
    Tuesday, April 20, 2010 3:25 PM

All replies

  • The database engine doesn't guarantee that there are some messages waiting to be received whenever an activated procedure is executed. Therefore you should code it to handle the case where RECEIVE returns an empty result set. Another good idea would be to have a loop that keeps receiving messages until the RECEIVE statement returns an empty result set. See here for an example of a properly coded activated procedure.
    • Proposed as answer by Pawel Marciniak Wednesday, April 21, 2010 4:14 PM
    • Marked as answer by SQLSQL2018 Wednesday, May 12, 2010 8:50 PM
    Tuesday, April 20, 2010 3:25 PM
  • Thank. That was helpful. Will look into it.
    Tuesday, April 20, 2010 4:43 PM
  • Also, if the sending procs are ending their conversations  properly, that will send an END_CONVERSATION message that the Activation procs may pick up.
    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Wednesday, April 21, 2010 9:16 PM