Using variables in SQLCMD for Linux

Question

I'm running the Microsoft SQLCMD tool for Linux (CTP 11.0.1720.0) on a Linux box (Red Hat Enterprise Server 5.3 tikanga) with Korn shell. The tool is properly configured, and works in all cases except when using scripting variables.

I have an SQL script, that looks like this.

SELECT COLUMN1 FROM TABLE WHERE COLUMN2 = '$(param1)';

And I'm running the sqlcmd command like this.

sqlcmd -S server -d database -U user -P pass -i input.sql -v param1="DUMMYVALUE"

When I execute the above command, I get the following error.

Sqlcmd: 'param1=DUMMYVALUE': Invalid argument. Enter '-?' for help.

Help lists the below syntax.

[-v var = "value"...]

Am I missing something here?

user_1306784 · Answer

I think you're just not quoting the input variables correctly. I created this bash script...

#!/bin/bash
# Create a sql file with a parameterized test script
echo "
set nocount on
select k = '-db', v = '\$(db)' union all
select k = '-schema', v = '\$(schema)' union all
select '-', 'static'
go" > ./test.sql

# capture input variables
DB=$1 
SCHEMA="${2:-dbo}"

# Exec sqlcmd
sqlcmd -S 'localhost\lemur' -E -i ./test.sql -v "db=${DB}" -v "schema=${SCHEMA}"

... and tested it like so:

$ ./test.sh master
k       v     
------- ------
-db     master
-schema dbo
-       static

Using variables in SQLCMD for Linux

4 Answers