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:


SET XACT_ABORT ON
GO
BEGIN TRANSACTION
GO
SQL object 1 (CREATE/ALTER/INSERT etc…)
GO
SQL object 2 (CREATE/ALTER/INSERT etc…)
GO
next SQL batches if needed, each followed by GO
COMMIT
GO

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:


SET xact_abort ON
GO
BEGIN TRANSACTION
GO
CREATE TABLE [dbo].[People]
(
[id] [INT] IDENTITY(1, 1) NOT NULL,
[name] [NVARCHAR](max) NOT NULL,
[timestamp] [TIMESTAMP] NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (pad_index =
OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks =
on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]
textimage_on [PRIMARY]
GO
CREATE PROCEDURE [dbo].[InsertPerson] @name NVARCHAR(max)
AS
BEGIN
INSERT INTO dbo.People
(NAME)
VALUES (@name);
END
GO
INSERT INTO dbo.Persons run-time error – dbo.Persons table does not exist
(NAME)
VALUES ('Dawid')
GO
COMMIT
GO

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:


Msg 208, Level 16, State 1, Line 33
Invalid object name 'dbo.Persons'.
Msg 3902, Level 16, State 1, Line 39
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

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:


INssSERT INTO dbo.Persons syntax error – 'INssSERT' instead of 'INSERT'
(NAME)
VALUES ('Dawid')

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:


Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'INTO'.

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:


INssSERT INTO dbo.Persons (NAME) VALUES ('Dawid')
GO
if @@error != 0 raiserror('Error in script execution', 20, 1) with log
GO

https://gist.github.com/dsibinski/f8a9a5dea9606db4a953d7e3f04dba7c/edit

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:


Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'INTO'.
Msg 2745, Level 16, State 2, Line 37
Process ID 53 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 37
Error in script execution
Msg 596, Level 21, State 1, Line 36
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 36
A severe error occurred on the current command. The results, if any, should be discarded.

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!

 

.NET/Full Stack Developer and digital nomad
0 0 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Youenn
4 years ago

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 ­čÖé

Anonymous
Anonymous
4 years ago

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.