Using the SQLCMD integration and run-with attribute with Liquibase Pro and MSSQL Server

Prerequisites

  • Use Liquibase 4.8 or later.
  • Specify how Liquibase can find SQLCMD by adding SQLCMD to your PATH. Alternatively, pass its location in the liquibase.sqlcmd.conf file or from the command prompt during runtime. See liquibase.sqlcmd.path in SQLCMD integration arguments.

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

Using the SQLCMD integration

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 run-with attribute to the needed changesets in the changelog you use:
    • run-with:sqlcmd for an SQL changelog
    • run-with="sqlcmd" for an XML changelog
    • "run-with": "sqlcmd" for a JSON changelog
    • run-with: sqlcmd for a YAML changelog

    Note: See run-with and sqlcmd examples.

  1. Specify the SQLCMD integration arguments in one of the following ways:
    • Add the values in liquibase.sqlcmd.conf or the Liquibase property file.

    Note: You can use the liquibase.sqlcmd.conf file along with the Liquibase properties file. If you use Liquibase Pro and SQLCMD in automation, set arguments in the liquibase.sqlcmd.conf file instead of running them every time at the command prompt.

    • Set the values as environment variables
    • Run the values as Java system properties (JAVA_OPTS) along with any command at the command prompt:
  1. Run a Liquibase command:
  2. Example: liquibase update --changelog-file=sqlcmd_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 will occur. Liquibase will only ignore it.

Using 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 run-with 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

Property Environment variable CLI Description

liquibase.sqlcmd.args

LIQUIBASE_SQLCMD_ARGS

--sqlcmd-args

[Optional] Extra arguments to pass to the sqlcmd executable. For more information about SQLCMD arguments, see sqlcmd Utility documentation.

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

liquibase.sqlcmd.keep.temp

LIQUIBASE_SQLCMD_KEEP_TEMP

--sqlcmd-keep-temp

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

liquibase.sqlcmd.keep.temp.name

LIQUIBASE_SQLCMD_KEEP_TEMP_NAME

--sqlcmd-keep-temp-name

[Optional] Flag to indicate the name of a temporary SQL file after the execution of SQLCMD.

liquibase.sqlcmd.keep.temp.overwrite

LIQUIBASE_SQLCMD_KEEP_TEMP_OVERWRITE

--sqlcmd-keep-temp-overwrite

Flag to overwrite any files in the specified directory with the same name. Default: true.
liquibase.sqlcmd.keep.temp.path LIQUIBASE_SQLCMD_KEEP_TEMP_PATH --sqlcmd-keep-temp-path [Optional] Flag to indicate the location to store a temporary SQL file after the execution of SQLCMD. If not specified, the files are stored in the system's temp directory.
liquibase.sqlcmd.path LIQUIBASE_SQLCMD_PATH --sqlcmd-path Path to the sqlcmd executable.
liquibase.sqlcmd.timeout LIQUIBASE_SQLCMD_TIMEOUT --sqlcmd-timeout

Seconds to wait for the sqlcmd timeout:

  • "-1" disables the timeout
  • "0" returns an error
  • 1800 seconds (30 minutes) is the default value

SQLCMD 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 SQLCMD timeout. In your liquibase.sqlcmd.configliquibase.sqlplus.config file, add liquibase.sqlcmd.timeout=nn, where nn is time in seconds to wait before stopping the process.
  • Save the output of your SQLCMD spool files to your temp directory by adding liquibase.sqlcmd.keep.temp=true to the liquibase.sqlcmd.config file.

Related links