views:

528

answers:

1

I've got a stored procedure that puts a message on a SQL Server Service Broker message queue. I need to return an error message from the stored procedure if something goes wrong and the message doesn't get placed on the message queue. The problem I am seeing is that even if the SQL Server Service Broker is disabled (which is how I'm trying to test the failure to put a message on the queue) it doesn't return an error when I run TSQL code to put a message on the queue.

Does anyone know how to detect if putting a message on a SQL Server Broker message queue failed?

+6  A: 

Service Broker does not place the message into the target queue. Instead is placed into the database transmissions queue (sys.transmission_queue). After the SEND is committed the message is picked up by the background transmitter, the routing is resolved and the message is delivered to its destination.

If the destination happen to be located within the same instance then a shortcut delivery path is attempted during the SEND statement itself, in which the message is placed straight into the destination queue. If the enqueue fails, then the message is bounced back and placed in the normal delivery path, ie. put into sys.trasnmission_queue. A message stuck in sys.transmission_queue will have a transmission_status explaining why the message could not be delivered. The system will automatically re-attempt these messages.

The only time when the SEND can return an error is when the message cannot be sent, not when it cannot be delivered. This would be if you try to SEND on a closed conversation or if there is some non-broker error (read only database, log full, out of memory etc) that prevents even accepting of the message into sys.transmission_queue.

This behavior of macking even a local delivery asynchronous and loosely coupled is intentional and is designed to help applications by behaving the same when the destination queue is local and when the destination queue is remote.

The way to handle errors in Service Broker is by checking your own queue for a response. If the destination service actively refuses your message (ie. access denied, or XML malformat, or service contract violation) then it will end the conversation with error and you get an error message back in your own queue. If your message simply cannot be delivered, then it will stay in transmission queue until it expires and then the conversation is ended with error, and again an error message will be placed into your own queue. Messages time out after the lifetime specified with BEGIN CONVERSATION expires.

So in your case when you disable the broker the message is still accepted, but not delivered. It is placed in the transmsision queue. As soon as you enable back the broker in the database, that message will be picked up and delivered. This behavior makes application writing much simpler when reliability of delivery is the main concern. The app simply SENDs knowing that the message will get there even if the destination is not available (eg. taken down for service maintenance), even if it takes hours or days or even weeks for the message to be delivered. For applications that are concerned also with the timing of delivery, they should specify a conversation lifetime. The system will attempt to deliver the message within this lifetime or give up. If it gives up, it notifies the sender by enqueueing an error message (the conversation timeout error).

Also applications should not wait for a response. They should SEND, COMMIT and continue, be event driven. When the target sends back a response, or when the underlying infrastructure notifies of an error, the application gets a message in its own queue and it should react to that message.

Remus Rusanu
+1 well explained
KM