Can't migrate proc on SqlServer 2022

General Discussion
1

SqlServer 2022, LiquiBase 5.0.1

I’m new to Liquibase. I finally got it running and have had success with tables and views. Now I need to migrate a proc and I have searched and I have tried what I think is everything but I can’t get my first proc migration to work. This semicolon delimiter thing really sucks. In the past I’d written my own migration tools and also used 3rd party tools that generated migration scripts (redgate, dbstudio, etc) and never had an issue. I can’t believe this semicolon thing. The proc has to have semicolon because sqlserver needs them.

I’ve tried various forms of:

endDelimiter:GO

endDelimiter:\nGO

endDelimiter:GO splitStatements:false

endDelimiter:$$ splitStatements:false

if I use none of the above, from the output it appears it makes it past the 1st few statements and then bombs when it gets to a semicolon inside the proc. As soon as I try adding any other settings to changeset line, it looks like it starts bombing on the 1st statement. Or then it will complain that create needs to be the 1st statement,

I find posts mentioning using custom delimiters like $$ which I tried and didn’t work, but then I also would have a script file that sqlserver would not like itself either.

The only way I managed to get it to work was to break each proc out into it’s own file, remove all the top so that create was the first valid sql line and use endDelimiter:GO splitStatements:false. But that is unacceptable. I need to be able to have migration script files that include many things like tables, view, multiple procs, etc wrapped in a transaction.

Is there any good example I’ve missed of how to get MULTIPLE procs to migrate in such a way that the script is valid for both liquibase and sql server using a delimiter that sql server is happy with too and the whole thing is wrapped in a transaction?

Up until now I’d hand write my script in ssms or use one of those tools to generate a script with a lot of stuff, I’d test running them in ssms or let those tools execute them directly, and then I’d save the file to my liquibase folder and add the top comments. Liquibase was handling that just fine until now with my 1st proc, seems like I can’t do that anymore.

How is it that sql ssms, redgate, dbstudio are capable of generating and running scripts like below, but liquibase can’t? What am i missing?

The top of my migration file starts with

--liquibase formatted sql
--changeset mike:1 endDelimiter:GO splitStatements:false
--comment: adding search features


SET LANGUAGE 'English'
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT, XACT_ABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
GO

--
-- Start Transaction
--
BEGIN TRANSACTION
GO

CREATE PROCEDURE [dbo].[SearchProducts]
		@JsonData NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @OutputDebugInfo BIT = 1

..... eventually a ;with and then another proc, more tables, view, procs, data, etc
2

It would help to have the entire changeset, but it looks to me like you need to add a “GO” to indicate the end of your CREATE PROCEDURE statement.

3

I do have a GO after that. The issue is liquibase is throwing an error and complaining about the sql before even making it to my create even though if I paste the same sql into ssms it works. Below is a bit more but with stuff removed to keep it short. If I remove the changset options at the top, then it makes it past what it’s complaining about at the top and then complains about the semicolon

--liquibase formatted sql
--changeset mike:1 endDelimiter:GO splitStatements:false
--comment: adding search features


SET LANGUAGE 'English'
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT, XACT_ABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
GO

--
-- Start Transaction
--
BEGIN TRANSACTION
GO

CREATE OR ALTER PROCEDURE [dbo].[SearchProducts]
		@JsonData NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	select * from dbo.Product
	
	-- other stuff that has semicolon that messes up liquibase

END
GO



CREATE OR ALTER PROCEDURE [dbo].[SearchProductsEx]
		@JsonData NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	select * from dbo.Product

END
GO


COMMIT TRANSACTION
GO
4

Thanks for including the changeset, now please post the entire error message.