Help with PS wretched way of passing parameters to executables

With all the scripting languages I have worked with I had never had so much
trouble passing parameters to external executables as I do with PS.

My problem is simple but I have not gotten that to work. I am using
PowerShell v1.0 on Vista and I want to execute the SQL Server 2005
SQLCMD.EXE utility from within a PowerShell script. I need to use the
SQLCMD -v command line option to pass scripting parameters. The SQLCMD.EXE
syntax from a command line would be:

sqlcmd.exe -v TestVar1=value1 TestVar2=value2

Now from PowerShell I need to be able to use value1 and value2 as PS
variables so I expected something like this would work within PS:

$psVar1 = 'Value with spaces'
$psVar2='AnotherValue'
sqlcmd.exe -v TestVar1='$psVar1' TestVar2='$psVar2'

The issue is that no matter how I attempt to pass these to the sqlcmd.exe on
PS I get the annoying error:

Sqlcmd: 'TestVar1=Value with spaces': Invalid argument. Enter '-?' for
help.

I have noticed that IF the value contains no spaces (a single word) then it
is passed correctly to the executable, whereas if it contains spaces
(multiple words) then it craps out thinking it is multiple arguments or
something like that.

Passing it from DOS is no problem but I need to do it from within PS and
make sure SQLCMD.EXE is being executed from a user-specified directory. The
Invoke-SqlCmd commandlet seems to be only for SQL 2008 because I get an
error if I try to use that, for that reason I am directly invoking
SQLCMD.EXE

Any ideas what on earth am I doing wrong?

Hi,

Two things:

1. If you want your variables to be expanded when they are emebeded in strings,
wrap'em with double quotation marks, single quotation marks disables variable
substition:

PS > $psVar1 = 'Value with spaces'
PS > 'this is a test: $psVar1'

this is a test $psVar1



You can see that the value of $psVar1 is not expanded. However the following
does:

PS > "this is a test $psVar1"
this is a test: Value with spaces


Try adding an extra set of quotes. One set of quotes is consumed by PowerShell
so sqlcmd gets the command without them:

sqlcmd.exe -v TestVar1="'$psVar1'" TestVar2="'$psVar2'"



Now, you must be thinking: hey, you just said that strings inside single
quotes are not expanded, right?
Well, if the outer quotes are doubled then you're safe.... test it:


PS > "this is a test '$psVar1'"
this is a test 'Value with spaces'


Hope this make sense


---
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

Thanks Shay,
I knew about the double quotes thing. The double plus single does indeed
the trick in terms of PS. However I still get the "invalid argument" but
from SQLCMD. Apparently SqlCmd.exe does not quite work as advertised :/ so
PS is cleared...


"Shay Levi" <no@xxxxxx> wrote in message
news:89228ed22c7078ca895c8c114a15@xxxxxx

"....DotNet4Ever...." <hate.spam@xxxxxx> wrote in message
news:#BtFrVzuIHA.3780@xxxxxx The best way to figure out these problems is to be able to see exactly what
the EXE is getting from PowerShell. If you have the PowerShell Community
Extensions installed then you have a utility called echoargs that is meant
to solve these sort of problems. Just replace sqlcmd.exe with echoargs.exe
on your command line and you will see what PowerShell is passing to
sqlcmd.exe e.g.:

$psVar1 = 'Value with spaces'
$psVar2='AnotherValue'
echoargs -v TestVar1='$psVar1' TestVar2='$psVar2'
Arg 0 is <-v>
Arg 1 is <TestVar1=$psVar1>
Arg 2 is <TestVar2=$psVar2>

Hmm, perhaps this is what you want:

23> echoargs -v "`"TestVar1=$psVar1`"" "TestVar2=$psVar2"
Arg 0 is <-v>
Arg 1 is <TestVar1=Value with spaces>
Arg 2 is <TestVar2=AnotherValue>

--
Keith
http://www.codeplex.com/powershellcx

Tried posting two times with my findings but OE always screwed up.

Anyway, the passing of arguments remains a big issue, never had such problem
with the myriads of scripting languages I have used before.

The only way I managed to have PowerShell to pass the variables correctly to
the SQLCMD external application was to use user environment variables.

Emilio
http://www.virtual-aviation.info/
http://www.panamasights.com/

"Keith Hill [MVP]" <r_keith_hill@xxxxxx_no_spam_I> wrote in message
news:0926B4D9-CDD1-4A7B-893B-F112FAD4DE60@xxxxxx