no message error prevention

Question

I am using the following code to check whether a message exists on the specified queue:

 

Code Block

DECLARE @RecvReplyMsg NVARCHAR(100);

DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@RecvReplyDlgHandle = conversation_handle,

@RecvReplyMsg = message_body

FROM TargetQueue;

END CONVERSATION @RecvReplyDlgHandle;

SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;

GO

 

 

How can I prevent errors being thrown when there are no messages waiting on the queue? For example, if I send a message to the queue and run the above twice I get the following:

 

Msg 8418, Level 16, State 1, Line 11

The conversation handle is missing. Specify a conversation handle.

Answer 1

One method is to initialize @RecvReplyDlgHandle to NULL and check for NULL after the RECEIVE:

DECLARE 
	@RecvReplyMsg NVARCHAR(100) = NULL
	@RecvReplyDlgHandle UNIQUEIDENTIFIER = NULL;
 
BEGIN TRANSACTION;
 
RECEIVE TOP(1)
	@RecvReplyDlgHandle = conversation_handle
	,@RecvReplyMsg = message_body
FROM TargetQueue;

IF @RecvReplyDlgHandle IS NOT NULL
BEGIN
	END CONVERSATION @RecvReplyDlgHandle;
END;
 
SELECT @RecvReplyMsg AS ReceivedReplyMsg;
 
COMMIT TRANSACTION;

The application can check ReceivedReplyMsg for NULL to indicate no message was received.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Hi Licken,

Please wrap your END CONVERSATION and SELECT ... in a:

Code Block
if(@@rowcount <> 0)
begin
  end conversation ...
  select  @RcvReplyMsg
end

---

Iric Wen

TechNet Community Support