If you are in a distributed transaction and you encounter an error and roll back
the transaction, you find that you cannot very much at all after you have rolled
back. For instance, you cannot call stored procedures.
This is really bad. A typical error handler goes:
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC error_handler_sp @@procid
If you know that this procedure will be called as part of a distributed transction,
you can write it differently, but this may be a general procedure, that was not
intended to be part of a distributed transaction originally. The situation here
is that you cannot even call error_handler_sp. This procedure could then
theoretically identify that you are in an aborted distributed transaction and
invoke a loopback procedure to write the the errorlog table. (Which it also
would if @@trancount > 0.)
See also https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=466739 where I call for functions
to detect distributed transactions.
I can understand that I can't perform new INSERT operations if I'm in a
distributed transaction. (Although it certainly is desireable.) But I should
still be able to perform control of flow like running a stored procedure.