sqlcmd -v parameter ignored in PowerShell

Question

I've read the documentation (sqlcmd Utility), but I can't get sqlcmd's -v parameter to work as expected.

Assuming this SQL script (echo.sql):

:setvar THE_PATH C:\Users\Craig\Desktop
PRINT 'THE_PATH: $(THE_PATH)'

:setvar THE_TOP 10
PRINT 'THE_TOP: $(THE_TOP)'

When run at the PowerShell prompt without the -v argument set:

PS> sqlcmd -E -S 'server' -d 'database' -i '.\echo.sql'
THE_PATH: C:\Users\Craig\Desktop
THE_TOP: 10

Setting the numeric variable (THE_TOP) are ignored:

PS> sqlcmd -E -S 'server' -d 'database' -i '.\echo.sql' -v THE_TOP=5

PS> sqlcmd -E -S 'server' -d 'database' -i '.\echo.sql'
THE_PATH: C:\Users\Craig\Desktop
THE_TOP: 10

If I eliminate the default value for THE_TOP in echo.sql, it reinforced the assertion that the parameter is being ignored:

:setvar THE_TOP
PRINT 'THE_TOP: $(THE_TOP)'

PS> sqlcmd -E -S 'server' -d 'database' -i '.\echo.sql' -v THE_TOP=5
THE_PATH: C:\Users\Craig\Desktop
THE_TOP: $(THE_TOP)

If I attempt to set the THE_PATH parameter, I get:

PS> sqlcmd -E -S 'server' -d 'database' -i '.\echo.sql' -v THE_PATH="C:\path"
Sqlcmd: ':\path': Invalid argument. Enter '-?' for help.

What is the correct -v syntax?

you're better off using the `Invoke-Sqlcmd` cmdlet. If you don't want to, pass an array to the `-v` parameter as described in the not accepted answer of this thread: http://stackoverflow.com/questions/12120106/how-to-bind-the-v-parameter-of-sqlcmd-from-a-powershell — SimonS, Apr 25 '16 at 14:52
When you say that something didn't work, you have to say _how_ it didn't work. (Remember, we can't see your screen.) — Bill_Stewart, May 01 '16 at 23:59

craig · Accepted Answer · 2016-04-28T16:00:36.643

OK, this is retarded.

If :setvar is used in the script:

:setvar THE_TOP
PRINT 'THE_TOP: $(THE_TOP)'

then you attempt to set it at the PowerShell prompt, you'll get an error:

PS> sqlcmd -E -S server -d database -i .\echo.sql -v THE_TOP=5
'THE_TOP' scripting variable not defined.

However, if you DO NOT set it in the script:

-- variable disabled
-- :setvar THE_PATH
PRINT 'THE_TOP: $(THE_TOP)'

then you attempt to set it at the PowerShell prompt, then it will work as (not really) expected:

PS> sqlcmd -E -S server -d database -i .\echo.sql -v THE_TOP=5
THE_TOP: 5

** edit **

To supply a dynamically-generated path as a parameter to a script file (extract.sql):

:out $(THE_PATH)\extract.csv
SELECT  *
FROM    the_table
WHERE   the_key = $(THE_ID)
...

Execute in PowerShell session:

PS> $cd = Get-Location
PS> sqlcmd -E -S 'server' -d 'database' -i '.\extract.sql' -v THE_ID=5 THE_PATH=`"$cd\build`"

sqlcmd -v parameter ignored in PowerShell

1 Answers1