SET XACT_ABORT ON
-
Hello Kindly tell me is it necessary to use SET XACT_ABORT ON in sql transaction or not? Regards
-
Hello Kindly tell me is it necessary to use SET XACT_ABORT ON in sql transaction or not? Regards
Basically, XACT_ABORT is a hint to SQL Server about the behaviour of a transaction if an error occurs. If XACT_ABORT is set to ON then the transaction will be rolled back. Now, why did I say it's a hint? The answer is because if XACT_ABORT is set to OFF, there's no guarantee that the transaction will roll back - it may or may not (depending on circumstances) choose to just roll back the statement that caused the error, allowing other parts to complete - or the entire transaction may be rolled back.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
Basically, XACT_ABORT is a hint to SQL Server about the behaviour of a transaction if an error occurs. If XACT_ABORT is set to ON then the transaction will be rolled back. Now, why did I say it's a hint? The answer is because if XACT_ABORT is set to OFF, there's no guarantee that the transaction will roll back - it may or may not (depending on circumstances) choose to just roll back the statement that caused the error, allowing other parts to complete - or the entire transaction may be rolled back.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads