Raise error if BCP command fails to dump data into a file

Question

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?

Answer 1

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.

Answer 2

The question is a bit vague with regards to how BCP is being executed, outside of it being done within a Stored Procedure. But since that is all we really know at the moment, I will assume that you are calling BCP.EXE from xp_cmdshell.

If you want to simply capture actual errors being thrown by BCP, then that is very easy to do since the ERRORLEVEL value is returned as an INT from the xp_cmdshell stored procedure:

DECLARE @ErrorLevel INT;
EXEC @ErrorLevel = xp_cmdshell
         N'BCP "SELECT * FROM sys.objects where 1=  " queryout C:\temp\BCPtest.txt -T -w ';
SELECT @ErrorLevel;

Returns:

1

If, however, you want to treat queries that complete successfully yet return 0 rows as an "error" condition, then that can also be done, it just requires a little more effort:

DECLARE @ErrorLevel INT;
EXEC @ErrorLevel = xp_cmdshell
   N'BCP "SELECT * FROM sys.objects where 1= 0 " queryout C:\temp\BCPtest.txt -T -w && (FORFILES /P C:\TEMP\ /M BCPtest.txt /C "CMD /C IF @fsize LSS 3 DEL C:\TEMP\BCPtest.txt" & IF NOT EXIST C:\temp\BCPtest.txt EXIT -3)';
SELECT @ErrorLevel;

Returns:

-3

Please note that the long command-line needs to be either kept as a single line, or placed into a .CMD script so that it works properly.

The additional logic, in a more readable format, is:

&& (
    FORFILES /P C:\TEMP\
             /M BCPtest.txt
             /C "CMD /C IF @fsize LSS 3 DEL C:\TEMP\BCPtest.txt"
    & IF NOT EXIST C:\temp\BCPtest.txt EXIT -3
   )

Explanation:

• && this operator will run the command on the right side only if the command on the left side completed successfully. The reason for using this operator is to allow BCP to set the ERRORLEVEL value if it encounters an error; the commands on the right side are only needed if BCP does not run into an error yet returns 0 rows.
• ( the parenthesis group the commands inside of them together. This allows us to run the FORFILES and IF commands only if BCP completes successfully. Otherwise, neither of the commands within the ( and ) are executed.

• FORFILES cycles through a list of files, specified by certain switches, and executes a command per each file that matches the criteria (similar to the find command in Unix).

  • /P is the starting path. It has to be a path and cannot contain the filename.
  • /M is the filename filter or "mask".

  • /C is the command to run for each file. It pretty much needs to start with CMD /C. The IF command tests the size of the file that is found via the @fsize variable that is substituted by FORFILES and if it is less than 3 (i.e. LSS 3) then it will just delete the file. In my testing I found that using either -c or nothing with BCP to indicate ASCII / VARCHAR output would result in an empty file of 0 bytes. But using the -w to indicate Unicode / NVARCAR output results in empty files of 2 bytes (which should be the Byte Order Mark). Hence testing for "size < 3" covers both scenarios.

    The reason for deleting the file is that it is something that can be tested for in the parent process. Because CMD /C is being used to run the command on files that are found by FORFILES, it is a sub-process and environment variables will not persist (similar to creating a local temporary table in Dynamic SQL) and exiting with an error code simply returns to the parent process like it was already going to. Creating an empty file as an indicator is an option, but then it either needs to be cleaned up or is clutter. And if the process is being considered an error for not returning any rows, then we don't want the file anyway.

• & this operator executes the command on the right side regardless of the success or failure status of the command on the left side.
• IF this does a simple test for the existence of the specified file and if that file does not exist, then it will run the specified command.
  • EXIT -3 this exits the current process (which is the top-level OS process started by xp_cmdshell) while setting the ERRORLEVEL value to -3. You can change the -3 to be any value you like, just be mindful to not use values already being used by BCP so that you are able to distinguish between them. The EXIT is only needed to be used explicitly if you want to set ERRORLEVEL (similar to either not specifying RETURN at the end of a stored procedure, or needing to specify it in order to pass back a non-0 value).