How to: Retrieve Information from a Service Broker Error Message (Transact SQL)
A message of type https://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.
To retrieve the information from a Service Broker error message
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.
Example
-- 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="https://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="https://schemas.microsoft.com/SQL/ServiceBroker/Error";
(/brokerns:Error/brokerns:Description)[1]',
'nvarchar(3000)')
);
RETURN 0;
END
GO