SQL Server Brokerage Error Handling
I have a stored procedure that puts a message on the SQL Server Service Broker message queue. I need to return an error message from a stored procedure if something goes wrong and the message is not put into the message queue. The problem I see is that even if the SQL Server service broker is disabled (which is how I try to check for not queuing a message), it does not return an error when running TSQL code to enter a message in the queue.
Does anyone know how to determine if a message could not be sent to the SQL Server broker message queue?
source to share
Service Broker does not put the message on the target queue. Instead, it is placed in the database transfer queue ( sys.transmission_queue
). After SEND is committed, the message is picked up by the background transmitter, routing is enabled, and the message is delivered to its destination.
If the destination is in one instance, then during the SEND instruction itself, an attempt is made to specify a delivery path for the shortcut, in which the message is put directly into the destination queue. If the error is unsuccessful, the message is returned and placed on the normal delivery path, that is. put in sys.trasnmission_queu
e. The message stuck in sys.transmission_queue
will have an transmission_status
explaining why the message could not be delivered. The system will automatically retry these messages.
The only time a SEND can return an error is when the message cannot be sent, not when it cannot be delivered. It would be if you try to execute SEND
on a closed chain or if there is an invalid error (read-only, full write, from memory, etc.) that prevents even receiving the message in sys.transmission_queue
.
This behavior in both asynchronous and loosely coupled LAN delivery is intentional and is intended to support applications where they behave the same when the destination queue is local and when the destination queue is deleted.
The way Service Broker handles errors is by checking your own response queue. If the destination service actively refuses your message (i.e. Access denied or XML-malformat or service contract violation), this will end the conversation with an error and you will receive an error message in your own queue. If your message simply cannot be delivered, it will remain in the transmission queue until it expires, and then the conversation ends with an error, and again the error message will be put into your own queue. The time to wait for messages after the expiration date specified with BEGIN CONVERSATION
.
So in your case, when you disable the broker, the message is still being received, but not delivered. It is placed in the transmission queue. Once you include the broker in the database, this message will be delivered and delivered. This behavior makes it much easier to write applications when delivery reliability is important. The app just SEND, knowing that the message will go there even if the destination is unavailable (eg taken down for maintenance), even if it takes hours or days or even weeks to send the message. For applications that also deal with delivery times, they must indicate the conversation lifetime. The system will try to deliver the message during this lifetime or refuse. If it refuses, it notifies the sender by posting an error message (conversation timeout error).
Also, apps don't have to wait for a response. They must SEND
, COMMIT
and continue, event-driven. When the target sends a response, or when the underlying framework notifies an error, the application receives a message on its own queue and must respond to that message.
source to share