11

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?

GPX
  • 3,506
  • 10
  • 52
  • 69
  • 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 Jul 26 '12 at 11:52
  • There is a SQLCMD tool for Linux? – Remus Rusanu Jul 26 '12 at 11:52
  • @Diego Yes, I've tried that. Pretty sure `-v` is the issue here. – GPX Jul 26 '12 at 13:11
  • 1
    @RemusRusanu Yup! More documentation here - http://msdn.microsoft.com/en-us/library/hh568447.aspx – GPX Jul 26 '12 at 13:12
  • Look, a flying pig just landed at my window... – Remus Rusanu Jul 26 '12 at 13:19

4 Answers4

17

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

Anand
  • 171
  • 1
  • 3
8

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
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • This works. Although I wonder why `-v` still shows up when I execute `sqlcmd -?` and yet it wouldn't work. – GPX Aug 01 '12 at 09:30
  • @GPX - my guess is that it never worked (because of the overlap with environment variables in various Linux shells using the `$` notation) and that the help was corrected between the CTP and release versions. – Ed Harper Aug 01 '12 at 10:10
1

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
PingPongOng
  • 96
  • 3
0

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
Xedni
  • 3,662
  • 2
  • 16
  • 27