SQL Server Export and import BCP capture error and quit

Question

Hi I am looking BCP exports/Imports to secondary server through a power shell user created tables for our ERP system
SQL version :2016 Enterprise
Windows 2019

problem description: I have created powershell script to export import user created tables using bcp utility. since its running though an script , I am looking to find how to quit(power shell script) the process when bcp process find an ERROR when its export /import data or any other way bcp can exit where there is an error ?

export
bcp sourcedatabase.dbo.table1 OUT \networksharedata\bcp\table1.bcp -S servername -T -w -a 65535

import

bcp destination.dbo.table1 IN \networksharedata\bcp\table1.bcp -f \networksharedata\bcp\table1.fmt -S servername -T -a 65535

thanks

Answer 1

Hi @Ashwan ,

Please add –e parameter in bcp command line.

-e err_file

Specifies the full path of an error file used to store any rows that the bcp utility cannot transfer from the file to the database. Error messages from the bcp command go to the workstation of the user. If this option is not used, an error file is not created.

If err_file begins with a hyphen (-) or a forward slash (/), do not include a space between -e and the err_file value.

As Olaf mentioned, the bcp will automatically exit when there is an error.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

??? Sorry, but your post is more then just completly unclear.

Hi I am looking BCP exports/Imports to secondary server through a power shell user created tables for our ERP system

BCP + AO + PS + user +ERP; so many terms in one sentence without any seeable relation.

process when bcp process find an ERROR

When BCP runs on error, it already terminates on it's own.

And what for error occurs, you haven't mentioned it in any way?

Answer 3

As others suggested, your post is somewhat unclear. If you want BCP itself to stop as soon as there is an error, you need to add the option -m0, because by default, BCP accepts 10 errors when loading data.

If you want the entire PowerShell script to terminate, that question may be better asked in a PowerShell forum. I would expect BCP to set %ERRORLEVEL% if there is an error. You should still use -m0, because I don't think BCP sets %ERRORLEVEL% if the number of errors is in the affected range.