All features described on this page are part of the Professional Edition.

You can purchase licenses for the Professional Edition on the Portal: https://portal.sqldsc.com.

SQL Script Usage #

SQL Scripts are run using SQLCMD.EXE. That must be present on your system and in your path.

Test Query #

The simplest form of the test query is:

resource "sql_script" "objects.sql" {
	script_file = "objects.sql"
	test_query = "SELECT * FROM sys.objects WHERE [name] = 'new_object'"
}

This expects the query to return one row.

Consider the following resource for a sql_script:

resource "sql_script" "objects.sql" {
	script_file = "objects.sql"
	expected_rows = 2 
	test_query = <<EOF

SELECT	[name]
FROM	tempdb.sys.objects 
WHERE	[name] = 'Obj1'

UNION

SELECT	[name]
FROM	tempdb.sys.objects 
WHERE	[name] = 'Obj2'

EOF 
}

• The <<EOF syntax is used to create a multi-line string. Avoid any spaces before or after the EOF.
• The expected_rows field says this query must return two rows. This allows you to confirm that multiple objects were created by the script.

Default Database #

• If a database is specified in the target (usually for Azure), the script and test query are run in that database.
• If a database is specified in the resource itself, the script and test query are run in that database.
• If a database is not specified, the test query and script are run in the default database and should probably include statements to set the appropriate database or query from a database-qualified table (i.e. FROM database.schema.object)
• If you are running a script in a database, but that database hasn’t been created yet, it will probably fail. So far the best way is to check for the database in the query. In the sample below, the query would fail without the initial check because the ZAdmin database doesn’t exist during the TEST phase. The check makes sure the query returns no rows so it will want to run the script.

resource "sql_script" "defrag_tables_2" {
	test_query = <<EOF
		IF DB_ID('ZAdmin') IS NULL RETURN; 
		SELECT * 
		FROM   ZAdmin.INFORMATION_SCHEMA.TABLES 
		WHERE  TABLE_NAME = 'dba_indexDefragAG'
EOF
	expected_rows = 1
	script = "index-defrag/dba_defrag_tables.sql"
}

Pushing Scripts #

If you update a script that creates an object, you have two ways to push that out to each target instance.

The first option is the push it using a command-line option:

sqldsc apply --push objects.sql

This tells this resource block to go ahead and run this script even if the test_query said not to.

The second option is to make the test_query a little smarter.

resource "sql_script" "new_obj.sql" {
	script_file = "new_obj.sql"
	expected_rows = 1
	test_query = <<EOF

		SELECT	[name]
		FROM	tempdb.sys.objects 
		WHERE	[name] = 'Obj1'
		AND		[schema_id] = SCHEMA_ID('dbo')
		AND		[modify_date] > '2020-05-20'

EOF 
}

In this example, we also test the modify_date of the object. Setting that value properly in the WHERE clause will cause it to only find the object that was modified after the specified date. If it can’t find an object modified after that date, it will want to run the script.

SQLCMD Variables #

Since sql_script is a wrapper for SQLCMD.EXE, it supports passing SQLCMD style variables to a script. Consider the following resource block:

resource "sql_script" "vars.sql" {
    script = "vars.sql"
    test_query  = "SELECT 'X' WHERE 1=0;"
    V1 = "first"
    V2 = "1"
}

The fields script and test_query are used for the script. Any other key values are passed as variables to SQLCMD.EXE When paired with this script…

SET XACT_ABORT OFF;
PRINT 'V1: $(V1)'
PRINT 'V2: $(V2)'
GO

…it generates this command line (with the path omitted for simplicity):

sqlcmd.exe -S "localhost" -i "error.sql" -E -b -X1 -v v1="first" -v v2="1"

This produces the following results:

V1: first
V2: 1

This can often be used to build stored procedures that need to point at different databases in different environments.