Handling Service Broker Errors

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles