Stephanie Giovannini (3/2/2012)
So, how would you fix the posted code so that the uncommittable transaction message isn't reported?I'm curious which COMMIT statement is causing the error. There's a COMMIT statement after the CATCH block inside the proc, and also a COMMIT in the outer transaction.
I suppose one (or both) of these should check XACT_STATE() before executing?
The easiest method to fix the error that prompted this question is to check for the Dialog ID (or Conversation ID) in the sys.conversation_endpoints system table (or view?). The error is not at a commit statement, rather at the INSERT INTO dbo.[BrokerErrors] is where it throws the Severity 16, unable to perform operations that write to the log.
Order of Exceptions:
1. SEND exception (Severity 16)
2. INSERT INTO (Severity 16) <-- this is returned to the calling SP/App, aka answer 3