Use SQLCMD and runWith on Microsoft SQL Server

This page describes how to use Liquibase with the SQLCMD native executor on a Microsoft SQL Server database.

Prerequisites

  • Use Liquibase 4.8.0 or later.
  • Add SQLCMD to your PATH environment variable. Alternatively, pass its location in the liquibase.sqlcmd.conf file or from the command prompt during runtime. See liquibase.sqlcmd.path in the SQLCMD integration arguments section.

Note: Liquibase searches the executor location in the following order: runtime arguments, .conf file values, and then your PATH.

Setup

Tip: To use Liquibase and SQLCMD with Windows Integrated Security, follow the steps from Using Liquibase and MSSQL Server with Windows Integrated Security and proceed with the following instruction.

  1. Add the runWith attribute to the needed changesets in the changelog:
    • SQL: runWith:sqlcmd
    • XML: runWith="sqlcmd"
    • YAML: runWith: sqlcmd
    • JSON: "runWith": "sqlcmd"

    Tip: For more information, see the runWith and sqlcmd examples section.

  2. Specify the SQLCMD integration arguments in one of the following ways:
    • Pass the values at runtime on the command line
    • Add the values to liquibase.sqlcmd.conf or the Liquibase properties file.
    • Set the values as environment variables
    • Run the values as Java system properties (JAVA_OPTS) along with any command at the command prompt:
      • set JAVA_OPTS=-Dliquibase.sqlcmd.<option>=<value> && liquibase <command> --changelog-file=my_script.sql
      export JAVA_OPTS=-Dliquibase.sqlcmd.<option>=<value> && liquibase <command> --changelog-file=my_script.sql
  3. Run a Liquibase command:

    4. Example: liquibase status --changelog-file=my_script.sql

    Note: If the command fails, you will receive an error message. However, if you add a property that is not used in Liquibase to the liquibase.sqlcmd.conf file, no error occurs. Liquibase only ignores it.

Use the SQLCMD integration with multiple databases

To use Liquibase and sqlcmd utility on an instance with multiple databases:

  1. Create the liquibase.sqlcmd.conf file for each database you use and ensure it is in the directory from which Liquibase runs the files
  2. In each liquibase.sqlcmd.conf file, add a line like the following one: 
    liquibase.sqlcmd.args=-v MYDATABASE=db_dev
  3. Add USE $(MYDATABASE) to each changeset that uses the runWith attribute

    4. Example:

    USE $(MYDATABASE)
CREATE TABLE [dbo].[course] (
[CourseId] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (30) NOT NULL,
[Detail] VARCHAR (200) NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC) 8);

The update command will replace the correct value at runtime.

SQLCMD integration arguments

Note: Syntax for each parameter is specified in kebab-case (CLI and flow file), camelCase (properties file and JAVA_OPTS), and MACRO_CASE (environment variable).

Syntax (--cli, propertiesFile, ENV_VAR) Type Description 
--sqlcmd-args
liquibase.sqlcmd.args
LIQUIBASE_SQLCMD_ARGS
 String

Defines extra arguments to pass to the sqlcmd executable. For more information, see sqlcmd Utility documentation.

Note: The delimiter for arguments is a space " ". Do not use a comma "," or semicolon ";".

 
--sqlcmd-catalog-name
liquibase.sqlcmd.catalogName
LIQUIBASE_SQLCMD_CATALOG_NAME
 String

Database to use when running SQLCMD.

 
--sqlcmd-keep-temp
liquibase.sqlcmd.keep.temp
LIQUIBASE_SQLCMD_KEEP_TEMP
 Boolean

Indicates whether or not to keep a temporary SQL file after the execution of SQLCMD. If true, the file is not deleted. Default: false.

 
--sqlcmd-keep-temp-name
liquibase.sqlcmd.keep.temp.name
LIQUIBASE_SQLCMD_KEEP_TEMP_NAME
 String

Indicates the name of a temporary SQL file after the execution of SQLCMD. If no file name is specified, a name is automatically generated. 

--sqlcmd-keep-temp-overwrite
liquibase.sqlcmd.keep.temp.overwrite
LIQUIBASE_SQLCMD_KEEP_TEMP_OVERWRITE
 Boolean

Overwrites any files in the specified directory with the same name. Default: true.

 
--sqlcmd-keep-temp-path
liquibase.sqlcmd.keep.temp.path
LIQUIBASE_SQLCMD_KEEP_TEMP_PATH
 String

Specify the path in which to store the temporary files after the execution of SQLCMD. If not specified, the files will be stored in the system's temp directory.

 
--sqlcmd-log-file
liquibase.sqlcmd.logFile
LIQUIBASE_SQLCMD_LOG_FILE
 String

Log file for SQLCMD output.

 
--sqlcmd-path
liquibase.sqlcmd.path
LIQUIBASE_SQLCMD_PATH
 String

Path to sqlcmd executable.

 
--sqlcmd-timeout
liquibase.sqlcmd.timeout
LIQUIBASE_SQLCMD_TIMEOUT
 Integer

Indicates seconds to wait for the sqlcmd timeout. -1 disables the timeout. 0 returns an error. Default: 1800 (30 minutes).

Formatted SQL changeset using the runWith attribute

--liquibase formatted sql
				
--changeset myauthorname:2314 runWith:sqlcmd
			
	DECLARE l_emp_name VARCHAR2(250);	
	l_emp_no NUMBER;	
	l_salary NUMBER;	
			
	l_manager VARCHAR2(250);	
	BEGIN	
	INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA');
	
	...	
	END;		
	/

XML changelog with the inline SQL changeset using the runWith attribute

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

<changeSet id="2" author="myauthorname" runWith="sqlcmd"> 
<sql>
	DECLARE l_emp_name VARCHAR2(250);
	l_emp_no NUMBER;
	l_salary NUMBER;
	l_manager VARCHAR2(250);
       BEGIN
	INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA');
	...
	END;
	/
</sql> 

</changeSet>

XML changelog pointing to an SQL file with raw SQL in it

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

<changeSet id="2315" author="myauthorname" runWith="sqlcmd">  
	<sqlFile
	path="my/path/file.sql"
... > 
</changeSet>

YAML changelog pointing to an SQL file with raw SQL in it 

databaseChangeLog:
   - changeSet:
       id: 1-yaml
       author: myauthorname
       runWith: sqlcmd
       changes:
       - sqlFile:
           path: person.sql
           relativeToChangelogFile: true

JSON changelog pointing to an SQL file with raw SQL in it 

{
	"databaseChangeLog": [
	   {
		"changeSet": {
		  "id": "1-json",
		  "author": "myauthorname",
		  "runWith": "sqlcmd",
		  "changes": [
			{
			      "sqlFile":{
				      "path": "person.sql",
				      "relativeToChangelogFile": "true"
				}
			 }
		   ]
	     }
       }

Best practices

  • Do not set the endDelimiter or splitStatements=true property on SQLCMD changesets. SQLCMD handles delimiters and statement splitting natively.
  • Prevent hanging queries by configuring the timeout. In your liquibase.sqlcmd.config file, add liquibase.sqlcmd.timeout=nn, where nn is time in seconds to wait before stopping the process.
  • Save the output of your spool files to your temp directory by adding liquibase.sqlcmd.keep.temp=true to the liquibase.sqlcmd.config file.

Related links