--transaction doesn't always work

[JordanMarr]

I have a single .sql file with a bunch of related schema refactoring steps in it:

-- Add 'DrawingLogSheetNos.Id' column
ALTER TABLE DrawingLogSheetNos
ADD [Id] uniqueidentifier NOT NULL DEFAULT (NEWID());
GO

-- Drop DrawingLogSheetNos composite key (ProjectId, SheetNumber)
ALTER TABLE DrawingLogSheetNos
DROP PK_DrawingLogSheetNos;
GO

-- Set 'Id' as PK
ALTER TABLE DrawingLogSheetNos
ADD CONSTRAINT PK_DwgLogSheets PRIMARY KEY ([Id]);
GO

-- Add a unique constraint for (SheetNumber, ProjectId)
ALTER TABLE DrawingLogSheetNos
ADD CONSTRAINT [UQ_DwgLogSheets_SheetNumber_ProjectId] UNIQUE ([SheetNumber], [ProjectId]);
GO

-- etc...

I am running grate with the following options, including --transaction / -t:

grate -cs "Server=appdev-sql2017;...;TrustServerCertificate=True" -t -f "./Migrations" --silent

I'm still working out errors in my script, so it hasn't fully completed without an error yet.
The problem is that sometimes the first step actually succeeds in creating the new [Id] column in the table! Other times, it does not.

Maybe I am misunderstanding the --transaction feature, but I thought it meant that it created a transaction for me which would roll everything back if any of the steps failed.

Do I still need to create a transaction manually, or is this perhaps an intermittent bug?

To Reproduce
Add multiple scripts in a single .sql file where the first one works and subsequent scripts will fail and run with the -t option. Sometimes the first step will commit despite the subsequent errors, and other times it will rollback.

Expected behavior
I would expect that the first script item should always rollback if any subsequent script fails.

Screenshots

Desktop (please complete the following information):

• OS: Windows 10

[erikbra]

Hi, @JordanMarr . I'm not entirely sure, but I think the issue is with the GO statement, which is not really a T-SQL statement, but a batch terminator. And, when you issue that, the script is committed even though the transaction is rolled back.

I think the behaviour is similar to what is described here, if you set XACT_ABORT ON in T-SQL: https://www.codejourney.net/t-sqlssms-transaction-rollback-in-scripts-with-xact_abort-on-go-statements-and-syntax-errors/

Could you try removing the GO statement from your scripts, and see if it works as expected then?

[JordanMarr]

Thanks for the link. After reading the blog post, I would agree that this issue was likely caused as a result of the way SQL Server reacts differently to runtime errors vs design time errors.