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: stackoverflow.com/questions/12120106/… — SimonS
– SimonS, Commented Apr 25, 2016 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
– Bill_Stewart, Commented May 1, 2016 at 23:59

craig · Accepted Answer · 2016-04-28 16:00:36Z

0

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`"

edited Apr 28, 2016 at 16:00

answered Apr 28, 2016 at 15:52

craig

26.3k29 gold badges131 silver badges224 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlcmd -v parameter ignored in PowerShell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related