I am having some problem to understand how transactions work on SQL Server, even after I thought I had all answers from the documentation.
To put it simply, I have a transaction at the beginning of a loong script (which gravely alters the schema), and if any statement fails it should result in a rollback.
On PostgreSQL this works without no problem.
An example to illustrate, on PostgreSQL:
BEGIN TRANSACTION; DROP TABLE t1; -- This results in a rollback, because t1 doesn't exist CREATE TABLE t1 (c1 int); -- This and following statements are never run COMMIT TRANSACTION;
Example on SQL Server:
SET IMPLICIT_TRANSACTIONS OFF -- SET XACT_ABORT ON -- Supposedly stop everything on error GO BEGIN TRANSACTION GO DROP TABLE t1 -- This results in an error GO CREATE TABLE t1 (c1 int) -- This is execute anyway? GO COMMIT TRANSACTION GO
Even though the script results in an error, it never aborts to rollback. And the table "[dbo].[t1]" exist in the schema.
Someone suggested wrapping the statements in a
TRY/CATCH block, but this does not work due to some schema alterations requiring be split up in batches. (E.g. renaming/adding columns, and later inserting data).
Many answers here does state that
SET XACT_ABORT ON should indeed result in the script to be aborted, but it does not.
What am I missing here?