The T-SQL keyword "TRAN" is not supported in liquibase splitstatmenets mssql

[AlexGruebel]

Search first

• I searched and no similar issues were found

Description

Context: I have a Changeset with the endDelimiter:GO. (The endDelimiter does not really matter...)

The changeset creates a stored procedure, inside the procedure I create a transaction with "BEGIN TRAN".

With the "BEGIN TRAN" keyword liquibase does not detect the endDelimiter properly and does not split the Statment via the endDelimiter.

Therefore it tries to execute the whole script as one command. Since GO is not really a T-SQL command the changesets fails.

Workaround

Don't use the TRAN keyword, use TRANSACTION.

Steps To Reproduce

execute changeset on an "empty" sql-server database called test:

--liquibase formatted sql
--changeset a:1 
create table test.dbo.person (
    id int primary key not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE person;

--changeset a:2 runOnChange:true endDelimiter:GO
CREATE OR ALTER PROCEDURE dbo.Test as 
BEGIN TRAN  
    TRUNCATE TABLE test.dbo.person;

    INSERT INTO test.dbo.person(id, name)    
    VALUES(1, 'Name1');
COMMIT

    INSERT INTO test.dbo.person(id, name)    
    VALUES(2, 'Name2');
GO

--some other stuff, that should be executed in the changeset which creates the procedure....
INSERT INTO test.dbo.person(id, name)    
VALUES(3, 'N');

Expected/Desired Behavior

The changeset gets executed successfully.

Liquibase Version

4.20.0

Database Vendor & Version

SQL-Server 2022

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Ubuntu

Additional Context

Log output

Starting Liquibase at 07:39:09 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
Running Changeset: example-changelog.sql::1::a
Running Changeset: example-changelog.sql::2::a

Unexpected error running Liquibase: Migration failed for changeset example-changelog.sql::2::a:
     Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'. [Failed SQL: (102) CREATE OR ALTER PROCEDURE dbo.Test as 
BEGIN TRAN  
    TRUNCATE TABLE test.dbo.person;

    INSERT INTO test.dbo.person(id, name)    
    VALUES(1, 'Name1');
COMMIT

    INSERT INTO test.dbo.person(id, name)    
    VALUES(2, 'Name2');
GO


INSERT INTO test.dbo.person(id, name)    
VALUES(3, 'N');]

For more information, please use the --log-level flag

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[nvoxland]

Thanks for the PR, @AlexGruebel