Sql-server – Unable to open BCP host data-file error

I believe that BCP does a two phase process. The first is done with a format only where the query isn't actually executed but it is parsed so that BCP can figure out the columns that will be returned and in the second phase it actually runs the query. You are failing on the first phase as the temp table doesn't exist so it can't be used for the format only option.

Basically the use of the temp table isn't going to work.

Powershell is your friend here. When working with cmd commands in Powershell you can use the $LASTEXITCODE variable to read the result of the command you executed.

The code below passes a BCP command into the Invoke-Expression cmdlet and captures it's output.

$OutputPath = "C:\temp\Numbers-20151230.dat"

try
{
    $Command = "bcp dbo.Numbers out $OutputPath -T -n -S Localhost\JamesA_Test -d UtilityDB"
    $Output = Invoke-Expression -command $Command

    if ($LASTEXITCODE)
    { 
        throw $Output
    }
}
catch
{
    Write-Host "BCP command failed: $Output"   
}

I'm not sure how you want to handle the error so I just used Write-Host to display the error for this example. You could log the error to the event log, a file, a table in SQL, etc.