PRB: BCP 'in' May Cause Exception When Input File Last Column Is Datatype DATETIME (274472)

SYMPTOMS

When a bulk copy program (BCP) "in" operation is performed and the last column of the input file is of datatype DATETIME, BCP may fail with an exception, and you receive the following error message:

bcp.exe - Application Error
The instruction at "0x41274d35" referenced at "0x00975873". The memory could not be "written".

CAUSE

This behavior can occur when the last column of the destination table is the datatype DATETIME and the input file contains a line feed as a row terminator rather than a carriage return/line feed pair. As a result, BCP is not able to differentiate between a new row and the current row.

The command line switches of the BCP utility are not intended to handle this set of conditions. This is because when the BCP switch "/r\n" (/r being the row terminator switch) is used, BCP automatically adds a carriage return and becomes "/r\r\n." Because the input data file does not have a carriage return, the exception is raised.

WORKAROUND

There are four workarounds available to avoid this problem:

Create the BCP output file with carriage returns to act as row terminators.
Use DTS and select {LF} line feed as the row terminator.
Open the BCP file with Microsoft Word, and then save it as TEXT, which adds the carriage return to the end of each row.
Use a FORMAT file. Generally in the format file, the row terminator is defined as "\r\n," which can be modified to just "\n."

Following is an example of doing a BCP with a format file modified to avoid the exception:

bcp pubs..loadtable in d:\loadtable.out /Usa /P /S. /f d:\bcp70.fmt

BCP works for this particular table and input file and this format file (bcp70.fmt):

8.0
10
1 SQLNUMERIC 0 19 "\t" 1 Column1 ""
2 SQLNUMERIC 0 19 "\t" 2 Column2 ""
3 SQLDATETIME 0 8 "\t" 3 Column3 ""
4 SQLCHAR 0 1 "\t" 4 Column4 ""
5 SQLCHAR 0 6 "\t" 5 Column5 ""
6 SQLNUMERIC 0 19 "\t" 6 Column6 ""
7 SQLNUMERIC 0 19 "\t" 7 Column7 ""
8 SQLNUMERIC 0 19 "\t" 8 Column8 ""
9 SQLNUMERIC 0 19 "\t" 9 Column9 ""
10 SQLDATETIME 0 0 "<B>\n</B>" 10 Column10 ""

A normally created format file would have the following as the last entry:

10 SQLDATETIME 0 0 "<B>\r\n</B>" 10 Column10 ""

If the file is modified manually to remove the \r, then the BCP works by using the modified format file and the original input file.

MORE INFORMATION

The command line parameters do not allow just \n. If the \n parameter is passed, then it results in a call to "unbackslash." When unbackslash is invoked, it is converted to \r\n. If the \r parameter is passed, it remains \r. Therefore, without a format file or Data Transformations Services (DTS), the command line of BCP currently cannot handle just \n.