Unable to run bcp command from PowerShell

Question

I have to call bcp.exe from a PowerShell script. My code is:

$dataBase = 'MyDb'
$ProdEinheitTable = 'dbo.ProdEinheit'
$ProzessdatenTable = 'dbo.Prozessdaten_aktuell'
$sqlServerUserName = 'sa'
$sqlServerPassword = 'Password'
$server = 'MSSQLLocalDB'

$bcp = & 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'

and I am calling the bcp utility like this:

$bcp_args = "$bcp $dataBase.$ProdEinheitTable IN $datFileName -f $fmtFileName -U $sqlServerUserName -P sqlServerPassword -S $server -n"
Invoke-Expression $bcp_args

It gives me the error

usage: : The term 'usage:' is not recognized as the name of a cmdlet, function,
script file, or operable program. Check the spelling of the name, or if a path
was included, verify that the path is correct and try again.
At line:1 char:1
+ usage: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe { ...
+ ~~~~~~
    + CategoryInfo          : ObjectNotFound: (usage::String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

If I remove & from the string I get the exception

C:\Program : The term 'C:\Program' is not recognized as the name of a cmdlet,
function, script file, or operable program. Check the spelling of the name, or
if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe MyDb.dbo ...
+ ~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\Program:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

How can I call bcp from PowerShell?

Answer 1

The reason why your code doesn't work the way you expect (aside from the fact that you shouldn't be using Invoke-Expression in the first place) is because your expectation seems to be that

$bcp = & 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'

would define some kind of command invocation alias. That is not the case. The statement directly invokes bcp.exe without arguments and stores the output of the command in the variable $bcp (regular output, not error output).

This should work:

$dataBase = 'MyDb'
$ProdEinheitTable = 'dbo.ProdEinheit'
$ProzessdatenTable = 'dbo.Prozessdaten_aktuell'
$sqlServerUserName = 'sa'
$sqlServerPassword = 'Password'
$server = 'MSSQLLocalDB'

$bcp = 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'

$bcp_args = "${dataBase}.${ProdEinheitTable}", 'IN', $datFileName,
            '-f', $fmtFileName, '-U', $sqlServerUserName,
            '-P', $sqlServerPassword, '-S', $server, '-n'

& $bcp @bcp_args

Answer 2

Try following:

$bcp = 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'
$bcp_args = "$dataBase.$ProdEinheitTable IN $datFileName -f $fmtFileName -U $sqlServerUserName -P sqlServerPassword -S $server -n"

Start-Process -FilePath $bcp -ArgumentList $bcp_args

Answer 3

Another way to do it with the call operator like here with an array for the args that somehow works right: https://com2kid.wordpress.com/2011/09/25/powershell-call-operator-using-an-array-of-parameters-to-solve-all-your-quoting-problems/

$bcp = 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe'
$bcp_args = echo $bcp $dataBase.$ProdEinheitTable IN $datFileName -f $fmtFileName -U $sqlServerUserName -P sqlServerPassword -S $server -n
& $bcp $bcp_args