Microsoft KB Archive/274472

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

Article ID: 274472

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q274472





SYMPTOMS
When a bulk copy program (BCP) &quot;in&quot; 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 &quot;0x41274d35&quot; referenced at &quot;0x00975873&quot;. The memory could not be &quot;written&quot;.



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 &quot;/r\n&quot; (/r being the row terminator switch) is used, BCP automatically adds a carriage return and becomes &quot;/r\r\n.&quot; 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 &quot;\r\n,&quot; which can be modified to just &quot;\n.&quot;

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 &quot;\t&quot; 1 Column1 &quot;&quot; 2 SQLNUMERIC 0 19 &quot;\t&quot; 2 Column2 &quot;&quot; 3 SQLDATETIME 0 8 &quot;\t&quot; 3 Column3 &quot;&quot; 4 SQLCHAR 0 1 &quot;\t&quot; 4 Column4 &quot;&quot; 5 SQLCHAR 0 6 &quot;\t&quot; 5 Column5 &quot;&quot; 6 SQLNUMERIC 0 19 &quot;\t&quot; 6 Column6 &quot;&quot; 7 SQLNUMERIC 0 19 &quot;\t&quot; 7 Column7 &quot;&quot; 8 SQLNUMERIC 0 19 &quot;\t&quot; 8 Column8 &quot;&quot; 9 SQLNUMERIC 0 19 &quot;\t&quot; 9 Column9 &quot;&quot; 10 SQLDATETIME 0 0 &quot;\n&quot; 10 Column10 &quot;&quot; A normally created format file would have the following as the last entry: 10 SQLDATETIME 0 0 &quot;\r\n&quot; 10 Column10 &quot;&quot; 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 &quot;unbackslash.&quot; 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.

Additional query words: 0x41274d35 0x00975873 freeze crash crashes stops responding bcp

Keywords: kbprb kbpending KB274472

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.