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.