Uncommitted transactions -- the "silent" problem
JHH
Posts: 3
Sometimes it can be the case -- particularly when running a complicated stack of scripts -- that Multi Script will execute a script in the stack that contains an unbalanced number of BEGIN TRAN and COMMIT statements. And if there are more BEGIN TRANSACTION statements than there are COMMIT statements that follow it, then you wind up with an uncommitted transaction.
Multi Script will not give you indication that this has happened when it does happen. The script shows in Multi Script as having successfully executed. And it appears that if this happens in the middle of a stack of scripts, then the remainder of the stack can end up being uncommitted -- perhaps because Multi Script runs the entire stack of scripts via one connection to SQL Server?
Multi Script needs to be enhanced to flag a script an uncommitted transaction as an execution error, so that the person using the tool is aware that transaction rollback has been triggered, probably at the end of the stack of scripts being executed.
Multi Script will not give you indication that this has happened when it does happen. The script shows in Multi Script as having successfully executed. And it appears that if this happens in the middle of a stack of scripts, then the remainder of the stack can end up being uncommitted -- perhaps because Multi Script runs the entire stack of scripts via one connection to SQL Server?
Multi Script needs to be enhanced to flag a script an uncommitted transaction as an execution error, so that the person using the tool is aware that transaction rollback has been triggered, probably at the end of the stack of scripts being executed.
Comments
Thanks for contacting us and sorry you are having this issue!
I have logged this as feature request SE-694, note that no work is planned on SQL Multi Script in the short term, but your request will be there when they pick up work again.
I have followed up with you via email if you have any other questions or issues.
Warm Regards,
Technical Sales Engineer
Redgate Software
@TRANSCOUNT is zero.
@TRANCOUNT AS CHAR(3))
@TRANCOUNT > 0
BEGIN
COMMIT;
END;
@TRANCOUNT AS CHAR(3))