MS SQL Server script generation puts DATABASECHANGELOGLOCK prior to USE statement

[jjamieson-va]

Search first

• I searched and no similar issues were found

Description

When generating a SQL file for Microsoft SQL Server using Ant, the update statement to lock the database is done prior to the USE statement. When running the SQL script in SQL Server Management Studio, if the DBA has a different database selected then this update statement will fail as the DATABASECHANGELOGLOCK table may not exist in the selected database.

The DATABASECHANGELOGLOCK table does exist in the database, which is why liquibase is not generating the SQL for creating the table

Steps To Reproduce

When generating a SQL script file for Microsoft SQL Server using Ant the update statement to lock the database is done prior to the USE statement.

Ant Task:

<target name="generateSQL">
		<mkdir dir="${sql.cycle.dir}"/>
		
		<property name="sql.cycle.script.name" value="PTS_${pts.version}_${target.env}_RELEASE_SCRIPT.sql" />
		<property name="sql.cycle.outputFile" value="${sql.cycle.dir}/${sql.cycle.script.name}" />
		<path id="liquibase.classpath">
				<fileset dir="${liquibase.lib.dir}" includes="*.jar"/>
				<fileset dir="${lib.dir}" includes="${sql.jar}" />
			</path>
		<taskdef resource="liquibasetasks.properties">
			<classpath refid="liquibase.classpath" />
		</taskdef>
		<updateDatabase changeLogFile="${lbprop.changeLogFile}" promptOnNonLocalDatabase="true" classpathref="liquibase.classpath" outputFile="${sql.cycle.outputFile}" contexts="${target.env}">
		    <database driver="${lbprop.driver}" url="${lbprop.url}" />
		</updateDatabase>
	</target>

This is what is generated by liquibase:
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log:
-- Ran at: 2/13/24, 2:57 PM
-- Liquibase version: 4.25.1
-- *********************************************************************

USE myDatabase;
GO
-- myDatabase does in fact contain the DATABASECHANGELOGLOCK table that should be updated

Expected/Desired Behavior

The update statement to lock should occur after the USE statement so the expected output of generating the SQL file should look like the following:
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log:
-- Ran at: 2/13/24, 2:57 PM
-- Liquibase version: 4.25.1
-- *********************************************************************

USE myDatabase;
GO

-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO

Liquibase Version

4.25-1

Database Vendor & Version

Microsoft SQL Sever 2019

Liquibase Integration

ant

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows

Additional Context

No response

Are you willing to submit a PR?

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

[tati-qalified]

Hi @jjamieson-va, thank you for reporting this issue.
I have been able to reproduce it with Liquibase version 4.26.0, both with and without Ant. In my case the changesets still ran with no issues, but I agree that the USE statement should be at the beginning of the script.

I'll be leaving this ticket open for the community to propose a fix. Our development team will be available to provide guidance to whoever submits a PR.

Thank you,
Tatiana

[jjamieson-va]

Thank you Tatiana, in most cases the generated script does run without issue. Where we see an error is when using SQL Server Management Studio (SSMS). If the user logged in to SSMS attempts to run the script and they have a database selected in SSMS (e.g. master instead of myDatabase as in the example code above) that does not have the DATABASECHANGELOGLOCK table in it, then the first statement of setting locked=1 fails. The rest of the script does run successfully, but it does create a scenario where the locking is not being applied correctly prior to running the rest of the script.