Powershell SQLCMD

Question

We were experiencing problems with Powershell and SQLCMD, when there was sapces in the -v parameter variable powershell wouldn't run the command.

e.g.

sqlcmd ... -v VAR="Some space"

Has anyone experienced this before or know how to fix the problem?

Thanks,

B

If you're using SQL 2008, I highly recommend using Invoke-SqlCmd instead (along with the other cmdlets & providers). — Richard Berg
– Richard Berg, Commented Aug 29, 2009 at 16:05

Anne Gunn · Accepted Answer · 2009-08-28 23:38:32Z

5

The syntax above works for the PS commandline but fails within a script.

We struggled a long time with how to make this work. One of our very clever QA guys finally came up with the following:

$variableWithSpaces="one two three"
$mySqlCmd = "sqlcmd -E -S $dbServer -i $script  -v var=```"$variableWithSpaces```" "
Invoke-Expression $mySqlCmd

Plug ugly but it works.

answered Aug 28, 2009 at 23:38

Anne Gunn

2,4671 gold badge30 silver badges43 bronze badges

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

1 Comment

Tried for 4+ hours, even building commands that looked great in echoargs but still failed. This finally did it. PSH rocks <s>.

Joey · Accepted Answer · 2009-06-18 20:54:39Z

2

Powershell will actually pass the parameter to the program as "VAR=Some space". Maybe sqlcmd stumbles over this. By using

VAR=`"Some space`"

instead it will get passed as VAR="Some space". Maybe that resolves the problem.

answered Jun 18, 2009 at 20:54

Joey

357k88 gold badges704 silver badges699 bronze badges