If the Rollback transaction is within the Catch Statement, why doesn't the transaction end?
Here's an example script I used for testing this out just now. The table I'm trying to update exists in a different database, so it fails
USE [MASTER]
BEGIN TRY
BEGIN TRANSACTION
Update DBAEmails Set Email = 'thisted@gmail.com' where ID = 2
COMMIT TRANSACTION
END TRY
BEGIN Catch
print 'Failure to Update Email'
RollBack Transaction
End Catch
I get the error message
Msg 208, Level 16, State 1, Line 4
Invalid object name 'DBAEmails'.
But the Rollback doesn't happen?
What you mention in your PS is true. We need to work out a better solution.