We have a situation where we are sending a message on a Service Broker queue as part of a distributed transaction. Obviously we only want the message sent if the transaction completes.
However we are finding that in this situation, the recieving end of the queue recieves the message before the transaction commits, somehow becoming enrolled too. This is causing knock on problems if the sender then rolls back, eventually leading to the queue
being disabled due to poison messages.
Is this expected behaviour? If so, how can we send messages transactionally?
There are many darkness of combining distributed transaction with Service Broker, and distributed transaction cannot be used with WAITFOR instruction. As for the poison message after rollback, it is a default behavior of Service Broker. You may follow the
Poison messages handling section in
this article to deal with it.