I wasn't very specific before. You need to move the
rollback tran a1
to be your first statement in the catch. everything prior to that is part of Tran a1, and therefore will be rolled back. That currently includes your error logging TRY...CATCH, meaning - nothing gets logged.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?