Sql-server – BCP utility:“Copy direction must be either ‘in’, ‘out’ or ‘format’”

bcpetlsql serversql-server-2008-r2sql-server-2012

I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error:

Copy direction must be either 'in', 'out' or 'format'.

The command did not change, only the location of the job and version of the instance.

bcp "SELECT * FROM [dbo].[Table]" queryout "\\filerepository\ftp\text_file.txt" -T -c -t"|" -S"ServerName" -d"MyDB"

Also, I ran this locally using the 2012 bcp.exe and received the same error. When I compare the books online article for the bcp utility, I do not see any changes which would affect the syntax of this command.

I would appreciate any advice.

Updated: The PowerShell Script

$ErrorActionPreference = "stop"
$serverInstance = "Server"
$databaseName = "DB"


# construct query
$query = @"
SELECT *
FROM [dbo].[Table]
"@

$query = $query -replace "`n|`r|`t"," "

# output results
$extractPath = "\\filerepository\ftp\"
$fileName = "CTBRI_$(Get-Date -Format "yyyy-MM-dd")_text_file.txt"
$path = Join-Path $extractPath $fileName;

$cmd = "`"$query`" queryout `"$path`" -T -c -t`"|`" -S`"$serverInstance`" -d`"$databaseName`""

bcp $cmd

if($LASTEXITCODE -ne 0)
{
    throw "bcp did not exit with a success code of 0. Hopefully there are more detailed messages above."
}

Best Answer

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016?

A parsing error that removes the double quotes around the query would result in this error.