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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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!
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 🙂
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.
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.