Handling Service Broker Errors

March 9, 2009

In the previous installment of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have been discussing the use of transactions in protecting integrity and consistency of Service Broker-based communication. As part of this discussion, we have presented a sample code illustrating this concept, consisting of T-SQL statements which, when executed on an initiator, started a conversation and sent a single message to a target after storing its content in a local auxiliary table. All of these actions were incorporated into a single transaction, which was either committed following their successful completion, or rolled back if any of them failed. In our example, we have leveraged the most basic form of BEGIN TRY/END TRY and BEGIN CATCH/END CATCH constructs, which provided error detection and controlled the flow of execution. We also mentioned one possible scenario, in which such a solution would yield the desired transactional behavior. However, it is important to note that, depending on the type of issue encountered by our code, the outcome might be different from expected. In this article, we will demonstrate a more robust approach to error handling and apply it to our target.

As you might recall, we have implemented an internally activated stored procedure dbo.cspProcessqRecv that retrieves new messages from qRecv queue and enters their content into a tbMsgRecv table. In our example, these two tasks were performed independently of each other, which could potentially lead to the situation where a message, despite being successfully received, does not have a corresponding table entry. One way to prevent such an occurrence involves enclosing both of these activities into a single transaction. In order to mitigate any unexpected issues, we will take advantage of the already familiar BEGIN TRY/END TRY and BEGIN CATCH/END CATCH blocks, as well as introduce a couple of additional improvements to our code. In particular, we will capture more specific information about a problem by invoking ERROR_NUMBER() and ERROR_MESSAGE() functions (available within the scope of TRY…CATCH construct) that return, respectively, error reference number and its brief description (which subsequently will be relayed to the initiator service via a Service Broker message). Furthermore, we will also employ a scalar function XACT_STATE, which returns one of three possible values indicating the status of a current, user-initiated transaction:

• 1 - denotes an active, committable transaction in progress
• 0 - signifies no active transaction
• -1 - designates a situation, in which an error prevents a current transaction from being committed, precluding any further action until a full rollback takes place.

In our error checking routine, we will take this information into account, deciding whether to force transaction rollback or invoke its commit based on the value returned by XACT_STATE. We will also introduce rudimentary mechanism for tracking failed messages. Note that this approach is completely arbitrary, so you might want to consider other means of dealing with errors, including preserving problematic messages in auxiliary tables or simply returning them to the queue (in hopes that the issue that prevented the current transaction from being committed was only temporary):

-- on srvEnt01
USE dbSBEnt01
GO

ALTER PROCEDURE dbo.cspProcessqRecv
   WITH EXECUTE AS OWNER
   AS
   BEGIN
      DECLARE @convHandle UNIQUEIDENTIFIER;
      DECLARE @msgTypeName SYSNAME;
      DECLARE @status TINYINT;
      DECLARE @srvName NVARCHAR(512);
      DECLARE @srvConName NVARCHAR(256);
      DECLARE @msgTypeValidation AS NCHAR(2);
      DECLARE @msgBody NVARCHAR(400);
      DECLARE @cmd AS NVARCHAR(50);
      DECLARE @errNum AS INTEGER;
      DECLARE @errDesc AS NVARCHAR(400);

      BEGIN TRY

         BEGIN TRANSACTION;
            WAITFOR(RECEIVE TOP(1)
               @convHandle = conversation_handle,
               @msgTypeName =  message_type_name,
               @status = status,
               @srvName = service_name,
               @srvConName = service_contract_name,
               @msgTypeValidation = validation,
               @msgBody = CAST(message_body AS NVARCHAR(400))
               FROM qRecv), 
               TIMEOUT	1000;
            IF(@@rowcount != 0)
               BEGIN
                  INSERT INTO 
                     tbMsgRecv(convHandle, msgTypeName, [status], srvName, srvConName, msgTypeValidation, msgBody)
                        VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody);
                  END CONVERSATION @convHandle;
               END
         COMMIT TRANSACTION;

      END TRY

      BEGIN CATCH

         SET @errNum = ERROR_NUMBER();
         SET @errDesc = ERROR_MESSAGE();

         IF (XACT_STATE()) = -1
            BEGIN
	 ROLLBACK TRANSACTION;
	 BEGIN TRANSACTION;
	    RECEIVE TOP(1)
	       @msgBody = CAST(message_body AS NVARCHAR(400))
	       FROM qRecv
	       WHERE conversation_handle = @convHandle;
	    SEND ON CONVERSATION @convHandle
	       MESSAGE TYPE [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] (@msgBody)
	    END CONVERSATION @convHandle
	       WITH ERROR = @errNum DESCRIPTION = @errDesc;
	 COMMIT TRANSACTION;
            END

         IF (XACT_STATE()) = 1 
            BEGIN
               INSERT INTO 
                  tbMsgRecvErr(convHandle, msgTypeName, srvName, srvConName, msgTypeValidation, msgBody)
                     VALUES(@convHandle, @msgTypeName, @srvName, @srvConName, @msgTypeValidation, @msgBody);
               END CONVERSATION @convHandle
                  WITH ERROR = @errNum DESCRIPTION = @errDesc;
               COMMIT TRANSACTION;
            END

      END CATCH;
   END

Under favorable circumstances, the code enclosed within the BEGIN TRY/END TRY block will simply extract a message from the qRecv queue, enter its content into the tbMsgRecv table, and finish the conversation. In case our procedure encounters an error that renders the transaction uncommittable, the rollback will be invoked by the ROLLBACK TRANSACTION statement, placing the message back in the queue. We will process it using another RECEIVE statement and send it back to the initiator service (or perform any other arbitrary action that makes sense from the perspective of application functionality) along with an error message (of type http://schemas.microsoft.com/SQL/ServiceBroker/Error) terminating the current conversation. If the transaction still can be committed (as indicated by the value of 1 returned by the XACT_STATE function), we will do so after inserting the newly processed message into another auxiliary table (referred in our example as tbMsgRecvErr, which, presumably, would not be a subject to the same constraints that apply to its counterpart tbMsgRecv), terminating the conversation and sending to the initiator service a message containing an error number and description (again, formed according to the http://schemas.microsoft.com/SQL/ServiceBroker/Error type).

Clearly our example could be further refined and enhanced. For example, we should make sure that the initiator can properly react to each type of message returned by its target (and vice versa), including those generated by our code as well as by Service Broker. In absence of such provisions, you might have to rely on built-in Service Broker mechanism dealing with error conditions, known as Poison Message Detection, which we will describe in the next article of our series.

» See All Articles by Columnist Marcin Policht