How to: Retrieve Information from a Service Broker Error Message (Transact-SQL)

SQL)

A message of type

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.

1. Declare a variable of type

to hold the error code.

2. Declare a variable of type

to hold the error description.

3. Declare a variable of type

to hold an XML representation of the message body.

the message body from

to

, and assign the results to the

variable of type.

5. Use the value function of the

data type to retrieve the error code.

6. Use the value function of the

data type to retrieve the error description.

7. 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 that ran.

https://schemas.microsoft.com/SQL/ServiceBroker/Error
CAST
END CONVERSATION WITH ERROR
-- 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
)