Receive statement returns null while select statement returns row

السؤال Receive statement returns null while select statement returns row

  • Monday, April 11, 2011 6:33 PM
     
     

    We had a system with a Service Broker that was working and returning messages using the following SQL:

    DECLARE @convHandle uniqueidentifier
    DECLARE @msgBody varbinary(MAX);
    WAITFOR (RECEIVE TOP(1) @convHandle = conversation_handle,
    @msgBody = message_body
    FROM ChangeMessages), TIMEOUT 5000
    if( @convHandle is not null)
    begin
        END CONVERSATION  @convHandle
    end

    select @msgBody;

    The customer executed a DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS and since then, the above SQL returns NULL, while running the below SQL does return a row:

    DECLARE @convHandle uniqueidentifier
    DECLARE @msgBody varbinary(MAX);

    select @convHandle = conversation_handle,
    @msgBody = message_body
    FROM ChangeMessages

    Unfortunately, we have no other information on what the results were before and/or after the DBCC command was executed. 

    My question is, will removing the Queue, Service, and Route and then re-adding them to the system fix this issue or is there something else that needs to be done in order for the receive statement to start working as expected again?

    Thanks,

    Renny