Handling Service Broker Error Messages
Service Broker applications must handle two types of error messages received from conversations: error messages created by an application that uses Service Broker and system messages created by Service Broker.
Reporting Application Error Conditions
Service Broker applications are typically systems that consist of code running asynchronously on different computers. The parts of the application communicate with each other by using messages sent on Service Broker conversations. The part of the application on one side of a Service Broker conversation can report application errors to the other side by sending error messages. The receiving part of the application must have code to detect an error message and correctly handle the error condition.
Service Broker applications can communicate errors by using either system-defined or application-defined message types.
System-Defined Error Messages
Use the WITH ERROR clause of the END CONVERSATION statement to report application errors that are severe enough to require ending the conversation. For example:
END CONVERSATION @ConversationHandle
WITH ERROR = 1234 DESCRIPTION = "The account specified in the invoice does not exist, verify the account number."
The END CONVERSATION WITH ERROR statement:
Generates a Service Broker system error message and sends it to the remote side of the conversation. The error messages use the system-defined https://schemas.microsoft.com/SQL/ServiceBroker/Error message type.
Ends the local side of the conversation.
The part of the application that receives the Error message should do any needed cleanup and end its side of the conversation.
An application can end an active conversation with an error at any time. However, if the remote side of the conversation has already ended the conversation, Service Broker will not send the error message to the remote side. Instead, Service Broker just ends the local side of the conversation and removes all messages for the conversation from the local queue.
Application-Defined Error Messages
You can use application-defined error messages to report errors that are not severe enough to end a conversation. The application designer can specify the following:
One or more message types that are used to communicate these application errors.
The logic for handling these message types.
The part of the application that encounters the error condition can do the following:
Perform any required cleanup for the local side of the conversation.
Build a message using the application-defined message type and send it on the conversation.
The remote part of the application that receives the error message must have code to recognize the error message and perform any required cleanup on its side of the connection.
Handling Error Messages
Application code receiving messages from Service Broker conversations must have logic to process error messages received from the conversation. The code must detect and handle the following:
Error messages generated by an application that uses an application-defined error message type.
Error messages generated by an application that uses the WITH ERROR clause of the END CONVERSATION statement. These error messages use the https://schemas.microsoft.com/SQL/ServiceBroker/Error message type and have a positive number in the Code element.
Error messages generated by Service Broker. These error messages use the https://schemas.microsoft.com/SQL/ServiceBroker/Error message type and have a negative number in the Code element. Service Broker creates an Error message when an error makes it impossible for Service Broker to continue the conversation. For example, Service Broker cannot locate the destination service because the service does not exist in the current instance and there is no entry for the service in the routing table. In this situation, Service Broker creates an Error message for the conversation.
The result set returned by the RECEIVE statement contains a message_type_name column. Code that receives Service Broker messages typically uses message_type_name to route each message to code that processes the associated message type.
The exact logic a program follows to handle the error depends on the application. One example is a program that uses message retention and requires compensating transactions when the task fails. When an error is received, the program queries the queue for the messages that have already been processed, performs compensating transactions, and then ends the conversation. In contrast, if the program only has to record that an error occurred, the program logs the error to a logging table and ends the conversation.
The Code element of the https://schemas.microsoft.com/SQL/ServiceBroker/Error message contains the error code. Error messages created by an application that uses END CONVERSATION WITH ERROR have a positive value for the error code. Error messages generated by Service Broker contain negative values for the error code. The Code value in messages generated by Service Broker is just the negated value of the error that caused the Error message. For example, when an XML validation error (error code 9615) occurs, the Database Engine creates an Error message that has a Code element that contains the value -9615.
Once an application receives an Error message, the program can no longer send messages on that conversation. The application handles the error and then ends its side of the conversation. If an application receives an application-defined error message type, the conversation is still available unless the remote part of the application also ran END CONVERSATION.
Error handling routines should be coded in such a way as to prevent poison messages. For more information, see Handling Poison Messages.