Run a shell command with arguments from powershell script

Question

I need to extract and save a some tables from a remote SQL database using bcp. I would like to write a powershell script to invoke bcp for each table and save the data. So far I have this script that creates the necessary args for bcp. However I can not figure out how to pass the args to bcp. Every time I run the script it just shows the bcp help instead. This must be something really easy that I am not getting.

#commands bcp database.dbo.tablename out c:\temp\users.txt -N -t, -U uname -P pwd -S <servername>
$bcp_path = "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe"
$serverinfo =@{}
$serverinfo.add('table','database.dbo.tablename')
$serverinfo.add('uid','uname')
$serverinfo.add('pwd','pwd')
$serverinfo.add('server','servername')
$out_path= "c:\Temp\db\"
$args = "$($serverinfo['table']) out $($out_path)test.dat -N -t, -U $($serverinfo['uid']) -P $($serverinfo['pwd']) -S $($serverinfo['server'])"

#this is the part I can't figure out
& $bcp_path $args

Joey · Accepted Answer · 2010-03-19 22:10:51Z

First of all, $args is an automatic variable; you can't set it, so any line like $args = foo will do nothing (even with strict mode on; though a complaint would have been nice).

Then you are only passing a single argument (the string) to the program. I contains spaces, but they are properly escaped or enclosed in parentheses, so the program only sees a single argument.

You'll need to use an array for arguments to a program, instead of a single string, if you want to store it in a variable beforehand. And you need to name it something different than $args:

$arguments = "$($serverinfo['table'])",
             'out',"$($out_path)test.dat",
             '-N','-t,',
             '-U',"$($serverinfo['uid'])",
             '-P',"$($serverinfo['pwd'])",
             '-S',"$($serverinfo['server'])"

& $bcp_path $arguments

Or, what I would prefer, actually, you can simply write it out in a single line which gets rid of most of the ugliness here:

$out_path = 'c:\Temp\db'
& $bcp_path $serverinfo['table'] out $out_path\test.dat -N '-t,' -U $serverinfo['uid'] -P $serverinfo['pwd'] -S $serverinfo['server']

Luke Puplett · Accepted Answer · 2012-12-20 16:59:21Z

1

Some command-line apps that need to accept crazy Gangnam-style arguments with slashes, quotes, double-quotes, equals, colons, dashes, a veritable cocktail.

PowerShell, in my experience, sometimes just can't cope. So I write out to a .cmd file and execute that from cmd.exe, like so:

echo $("Running command: " + $commandLine);

$rnd = $(([string](Get-Random -Minimum 10000 -Maximum 99999999)) + ".cmd");
$commandFilePath = $(Join-Path -Path $env:TEMP -ChildPath $rnd);
echo $commandLine | Out-File -FilePath $commandFilePath -Encoding ascii;

& cmd.exe /c $commandFilePath

Make sure you output as ASCII since the default Unicode might not play nice with cmd.exe (it barked at me and showed odd characters on my first attempt).

answered Dec 20, 2012 at 16:59

Luke Puplett

45.9k55 gold badges205 silver badges291 bronze badges

2 Comments

Vijay Over a year ago

see my question here. Please help if you can.

Luke Puplett Over a year ago

It's worth checking out Vijay's question since it has an answer that's better than others here if you are on PS V3.

Collectives™ on Stack Overflow

Run a shell command with arguments from powershell script

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related