Advanced 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 not database is specified, the test query and script are run in the default database and should probably include statements to set the appropriate database.

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 it 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.

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.