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: .gist table { margin-bottom: 0; } Firstly, we set XACT_ABORT to ON. This setting,

according to Microsoft docs read more...