Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

Copy in and error files

Copy in and error files

When you specify the -e error_file option with copy in, bcp stores the rows that it cannot copy in to Adaptive Server in the specified error file.

• The error file stores:

  • A line that indicates which row failed and the error that occurred, and

  • A line that is an exact copy of the row in the host file.

• If the file name specified after -e already exists, bcp overwrites the existing file.

• If bcp does not encounter any errors, it does not create the file.

bcp in detects two types of errors:

• Data conversion errors

• Errors in building the row; for example, attempts to insert a NULL into columns that do not accept null values or to use invalid data formats, such as a 3-byte integer

The copy in process displays error messages on your monitor.

The following example loads the newpubs file into the publishers database, storing any error rows in the pub_err file:

bcp pubs2..publishers in newpubs -epub_err

Keep the following in mind when working with error files generated by copy in:

• bcp stores rows in an error file only when the bcp program itself detects the error.

• bcp continues to copy rows until bcp encounters the maximum number of error rows, at which point bcp stops the copy.

• bcp sends rows to Adaptive Server in batches, so bcp cannot save copies of rows that are rejected by Adaptive Server, for example, a duplicate row for a table that has a unique index.

• Adaptive Server generates error messages on a batch-by-batch basis, instead of row-by-row, and rejects the entire batch if it finds an error.

• It is not considered an error for Adaptive Server to reject duplicate rows if either allow_dup_row or ignore_dup_key was set when a table's index was created. The copy proceeds normally, but the duplicate rows are neither stored in the table nor in the bcp error file.