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