CODEWITHSUNDEEP
sql-serverqueueservice-broker
ca9163d9
ca9163d9

Reputation: 29159

Sql Service Broker and transaction

All the examples on MSDN put the sending and receiving statements in transactions. The following script is from http://msdn.microsoft.com/en-US/library/bb839499(v=sql.100).aspx

Question 1: Why all the examples don't use begin try...end try begin catch...end catch to handle the exceptions?

Question 2: What if the message consuming/processing takes long time? Is it OK to have a long run transaction on SSB statements? What's the best approach?

Question 3: The following code doesn't end the conversation if the message name is not '//AWDB/1DBSample/RequestMessage'. Is it a bug?

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TargetQueue1DB
), TIMEOUT 1000;

-- Process.... May take a long time

IF @RecvReqMsgName =
   N'//AWDB/1DBSample/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE 
          [//AWDB/1DBSample/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;

Upvotes: 3

Views: 1642

Answers (1)

Serg
Serg

Reputation: 2427

Answer 1: All the examples don't use begin try...end try begin catch...end catch to handle the exceptions because they are examples - so they should be laconic and clear for understanding rather than contain code for implementation in production.

Answer 2: It is OK to have a long run transaction on SSB statements. SSB helps to avoid long run transaction in where it is critical. You can start asynchronous processing with SSB and go further immediately in your critical code. Maybe you should find another solution than SSB particularly for your case.

Answer 3: This is not a bug because RECEIVE TOP(1) may contain another message, for example, error message. So it seemed that you need to relocate your processing code inside IF...END section, which means you received correct message and should process it:

IF @RecvReqMsgName =
   N'//AWDB/1DBSample/RequestMessage'
BEGIN

 -- Process.... May take a long time

 DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE 
          [//AWDB/1DBSample/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

Upvotes: 1

Related Questions