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
Update DBAEmails Set Email = 'email@example.com' where ID = 2
print 'Failure to Update Email'
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.