After a process, we decide if we can commit using XACT_STATE=1. When an exception occurs, this will put -1 into XACT_STATE.

However, in some parts of the code, we have exceptions which we control. For example, when we try to convert a text into a date, if it doesn't work, an exception occurs. However, because it is inside a TRY/CATCH, the code can continue. For that particular scenario, we initialize with a blank date.

Then, we realized the commit still fails because XACT_STATE=-1. Thus, we replaced that condition with a variable such as CommitReadyToBeExecuted. However, the commit will not work because an exception occured before, even if we controlled it.

How can we reset XACT_STATE to 1 or force the commit to be executed even if we had an exception?