T-SQL/SSMS: transaction rollback in scripts with XACT_ABORT ON, GO statements and syntax errors

I’ve recently met a weird issue with T-SQL scripts at work and would like to share it with you today 🙂

T-SQL script with multiple objects created

On daily basis I work a lot with MS SQL Server databases. We often create many T-SQL objects (tables, views, procedures, functions) and because of some reasons we cannot use Entity Framework or another from widely available ORMs. Nonetheless, all objects created in the database must be kept in the form of SQL scripts (files) containing set of CREATE, ALTER, INSERT, DELETE or whatever T-SQL statements.

What we often do is to create a single .sql file, which in fact often contains more than one, separate (independent) SQL statements (e.g. creates a table and a procedure). What we obviously want to ensure is that when executing the script either all statements are committed to the database or none of them. This means that if in a part responsible for creating a particular object any SQL error is raised, execution of the whole script should be interrupted and the whole transaction rolled back, so in effect none of the objects contained within this script are created (none of the statements batches is committed). Here the issue comes out.

SQL script with XACT_ABORT ON and GO statements

In order to handle above-described requirements, the template for SQL script looks as follows:
Firstly, we set XACT_ABORT to ON. This setting, according to Microsoft docs, “specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error”. That’s what we want – in case of any errors the whole opened transaction should be rolled back.
Then we open a transaction with BEGIN TRANSACTION, which is committed to the server at the end of the script with COMMIT statement.
In between, we put the actual SQL statements that are responsible for creating new SQL objects or sometimes executing some procedure, inserting some data etc.
As can be noticed, each of these SQL “chunks” is followed by a GO statement. GO is one of the SQL Server statements, that are not T-SQL statements, but are recognized by the sqlcmd utility and SQL Server Management Studio (SSMS). GO statement allows to separate a script into batches. When GO is used, it signals the end of a batch and sends it to the SQL Server. It is necessary in order to separate T-SQL statements (e.g. to separate a procedure’s creation part from another procedure’s EXEC statement coming just after). That is also what comes in default in scripts generated by SSMS.
Of course, everything is in one, big transaction, so everything will be ok, right? When there is any error in any of the batches all other statements will be rolled back? Not necessarily!

XACT_ABORT ON and syntax errors

If we take a closer look at the above-cited description of XACT_ABORT from the Microsoft docs, it says that with this setting set to ON, transaction is rolled back “when a Transact-SQL statement raises a run-time error“. Bold part is crucial here – it only works for run-time errors.
Let’s consider the following sample .sql script:
Here we have 3 T-SQL batches:

  • Lines 7-17 contain a table’s creation statement
  • Lines 20-26 contain a procedure’s creation statement
  • Lines 29-31 contains an INSERT statement.

The last batch contains run-time error – there is no table dbo.Persons in the db. Execution of the above script in SSMS returns the following error:

What we expect to happen is that because the last batch produced run-time error, the table and the procedure will not be created. And exactly this happens – neither the table nor the procedure is created in the database. SET XACT_ABORT ON did its job. Uff.
Now, let’s modify the last, INSERT statement by introducing a syntax error so it looks as follows:
Full .sql script with this change is available here.

Does it change a lot? We will still get an error during the script’s execution, right? Yes – we will get the following error:

However, surprisingly, the table and the procedure are well-created in the database! The following screenshot presents:
Objects created despite an error
Why did it happen? Why XACT_ABORT set to ON the whole transaction was not rolled back? That’s because, after re-reading again-mentioned Microsoft docs, “compile errors, such as syntax errors, are not affected by SET XACT_ABORT“. Huh!

Solution

The only solution to this issue I found so far is to check, after each GO statement, if any errors occurred (using @@error) and if yes, interrupt the whole script’s execution using raiserror, for instance:
After introducing this solution to our example script, it looks as in this gist. After executing it in SSMS, the error is a bit different:
and neither the table nor the procedure is created in the database. That’s what we need.

Summary

I found this issue confusing and weird, especially that I could not find any equivalent to XACT_ABORT which would satisfy my needs and affect also syntax errors. The solution with checking for errors after each GO statement works fine, but is far from perfect.
Maybe you have some more experience in this topic and know any better solution? It’d be appreciated!  

3 thoughts on “T-SQL/SSMS: transaction rollback in scripts with XACT_ABORT ON, GO statements and syntax errors

  1. Cool article, I remember having the exact same issue a few years ago. A simpler solution would be to first run your script on a test database and catch any syntax error there 🙂

    1. Well, yeah, in most cases it is detected on test database. What’s more, a programmer who prepares the script should *normally* not commit to source control any script with syntax errors (so it should be tested on his local db even before the test one). The probability is relatively low, but it depends what you put in the scripts.
      In our case, as you may know ;), at the end of each script there is an EXEC statement responsible for setting script-related functionality’s version on the database. Many scripts may be executed by some external application (e.g. sqlcmd or C# code) and this issue (or its consequences) may be hard to notice/detect sometimes even on local/test db.

      It just surprised me I couldn’t find any other “T-SQL solution” for that.

      1. If I remember correctly, the problem with this EXEC/version was that people forgot to add a GO at the end of the stored procedure that was just above the EXEC/version statement, and therefore it became part of the stored procedure itself.

        One quick way to fix it would be to retrieve the current pre-execution of the script and compare it with the version post-execution. If they are the same, then it means that either something went wrong or someone didn’t properly version their script.

Leave a Reply

Your email address will not be published.