Using variables in SQLCMD for Linux

11
votes

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?

4
sql-serverlinuxsqlcmd
Have you tried, just for testing purposes, to run sqlcmd with only the "-v param1="DUMMYVALUE"" argument? Although the error refers to it, it would be good to be sure it's actually that argument that causes the issue. You can also put it as the first argument and see if it changes anything. – Diego
There is a SQLCMD tool for Linux? – Remus Rusanu
@Diego Yes, I've tried that. Pretty sure -v is the issue here. – GPX
@RemusRusanu Yup! More documentation here - msdn.microsoft.com/en-us/library/hh568447.aspx – GPX
Look, a flying pig just landed at my window... – Remus Rusanu

4 Answers

8
votes

In the RTP version (11.0.1790.0), the -v switch does not appear in the list of parameters when executing sqlcmd -?. Apparently this option isn't supported under the Linux version of the tool.
As far as I can tell, importing parameter values from environment variables doesn't work either.

If you need a workaround, one way would be to concatenate one or more :setvar statements with the text file containing the commands you want to run into a new file, then execute the new file. Based on your example:

echo :setvar param1 DUMMYVALUE > param_input.sql
cat input.sql >> param_input.sql
sqlcmd -S server -d database -U user -P pass -i param_input.sql
15
votes

You don't need to pass variables to sqlcmd. It auto picks from your shell variables: e.g.

export param1=DUMMYVALUE

sqlcmd -S $host -U $user -P $pwd -d $db -i input.sql

1
votes

You can export the variable in linux. After that you won't need to pass the variable in sqlcmd. However, I did notice you will need to change your sql script and remove the :setvar command if it doesn't have a default value.

export dbName=xyz
sqlcmd -Uusername -Sservername -Ppassword -i script.sql


:setvar dbName  --remove this line
USE [$(dbName)]
GO
0
votes

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