FIX: BCP Import with Extended Character as a Column Delimiter Fails (225705)

SYMPTOMS

Importing data into SQL Server using the bulk copy program (BCP) may fail if the field terminator is an extended character (that is, ASCII values 128-255). The problem only happens with import files that are over 32 MB in size.

WORKAROUND

To work around this following, do any one of the following:

Do not use extended characters as field terminators in the import file.
Keep the import file size below 32 MB in size.
Split the import file into several files, each of these being less than 32 MB in size.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

This problem has been observed with command line BCP and SQL-DMO BulkCopy.

For example, suppose you have a file called Myfile.txt that is over 32 MB in size. You will encounter this problem if you use the following command-line BCP operation:

bcp pubs.dbo.mytable in myfile.txt -c -t -Usa -P

No data will be imported to pubs.dbo.mytable and results similar to the following will be displayed after a short pause:

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 13079

Furthermore, specifying a value for the FirstRow and LastRow properties (-F and -L with command-line BCP) will not avoid this problem.