We are trying to configure Liquibase to run stored procedure changes in Microsoft SQLServer.
Those scripts contain batch terminators (GO statements) that cause liquibase changesets execution to fail (logs below) when used with includeAll changeset configuration
I know there is a configuration available using endDelimiter parameter but it is available for sqlfile type of change and we want to keep the includeAll option.
Something like this works but forces us to update configuration yaml everytime we want to add a sql file for execution:
Starting Liquibase at 11:39:44 (version 4.16.1 #4594 built at 2022-09-14 15:27+0000)
Liquibase Version: 4.16.1
Liquibase Community 4.16.1 by Liquibase
Running Changeset: changes/04_CreateSomeProcedure.sql::raw::includeAll
Unexpected error running Liquibase: Migration failed for changeset changes/04_CreateSomeProcedure.sql::raw::includeAll:
Reason: liquibase.exception.DatabaseException: Incorrect syntax near 'GO'. [Failed SQL: (102) USE [LiquibaseTest1]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE OR ALTER PROCEDURE [dbo].[AddRecordToTable2](
Insert into dbo.table2 values (@Id, @Description)
As I wrote, I need to keep an an approach where particular changesets are defined as path to folder not as a list of particular sql files. I do not really understand why liquibase’s tolerance to t-SQL syntax differs in both cases (includeAll vs SqlFile)
There isn’t a global end delimiter setting, since the times you should have to specify the end delimiter should be very small and what it actually takes to specify a delimiter can be complex and error prone.
You should not need to specify a delimiter for common things like ; and GO and /, especially with newer verisons like 4.16.1 that you are using.
If you remove the endDelimiter setting completely, does it work?
The thing is it does not work without delimiter. As You noticed I am using liquibase version that is up to date. I was pretty surprised to see that pretty common t-SQL syntax does not work out of the box for this configuration:
I really like Liquibase for its execution log and control over what has been done and what not. It’s really grat in this area, exactly what I’ve been looking for. It is just flexibility that it is lacking or am I missing something?
Glad to hear Liquibase is working well otherwise for you
I see where the trouble is: we have the contents of an include/includeAll raw sql file not being attempted to be split on delimiters. We did that back in the day when we didn’t have as smart of an SQL parser, since it would just split on ; even if it was in a stored procedure or something.
In effect, since we couldn’t trust our ability to split the statements, we rely on you to split them yourself if using includeAll.
The parser has improved, but it’s not always failsafe. But perhaps to the point where the number people with invalidly split SQL is lower that the number of people expecting SQL to be split?
In the meantime, you can control the splitting of the sql by converting your included sql to formatted sql by adding --liquibase formatted sql and a --changeset raw:includeAll splitStatements:true line at the beginning of the files.