Sql-server – Raise error if BCP command fails to dump data into a file

bcperror handlingsql serverstored-procedures

Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error in query written or an empty variable is passed to the BCP command.

Is there any way we can capture these events as errors and make it return some error code?

I am performing this from a stored procedure. Is there any way I can handle this in SP?

Best Answer

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.

Related Question