How to: Retrieve Information from a Service Broker Error Message (Transact SQL)
A message of type http://schemas.microsoft.com/SQL/ServiceBroker/Error is a Service Broker error message. Messages of this type are XML documents that contain a numeric code for the error and a description of the error.
-
Declare a variable of type int to hold the error code.
-
Declare a variable of type nvarchar(3000) to hold the error description.
-
Declare a variable of type xml to hold an XML representation of the message body.
-
CAST the message body from varbinary(max) to xml, and assign the results to the variable of type xml.
-
Use the value function of the xml data type to retrieve the error code.
-
Use the value function of the xml data type to retrieve the error description.
-
Handle the error as appropriate for your application. Errors with negative error codes are generated by Service Broker, errors with positive error codes are generated by service programs.
-- The variables to hold the error code and the description are
-- provided by the caller.
CREATE PROCEDURE [ExtractBrokerError]
( @message_body VARBINARY(MAX),
@code int OUTPUT,
@description NVARCHAR(3000) OUTPUT )
AS
BEGIN
-- Declare a variable to hold an XML version of the message body.
DECLARE @xmlMessage XML;
-- CAST the provided message body to XML.
SET @xmlMessage = CAST(@message_body AS XML);
SET @code = @@ERROR
IF @@ERROR<>0
RETURN @code
-- Retrieve the error code from the Code element.
SET @code = (
SELECT @xmlMessage.value(
N'declare namespace
brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
(/brokerns:Error/brokerns:Code)[1]',
'int')
);
-- Retrieve the description of the error from the Description element.
SET @description = (
SELECT @xmlMessage.value(
'declare namespace
brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
(/brokerns:Error/brokerns:Description)[1]',
'nvarchar(3000)')
);
RETURN 0;
END
GO
