FIX: The bcp utility does not import DBCS data from a text file that is larger than 32 MB

Article translations Article translations
Article ID: 883716 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

The bulk copy program (bcp) command prompt utility may not be able to import data from a text file when all the following conditions are true:
  • You run the bcp utility at the command prompt on a computer that has Microsoft Data Access Components (MDAC) 2.8 installed.
  • The text file contains information in a language that uses the double-byte character set (DBCS), such as Japanese, Korean, or Chinese.
  • The text file is larger than 32 megabytes (MB).

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft Windows XP. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
322389 How to obtain the latest Windows XP service pack
To resolve this problem, you may also obtain the hotfix that is mentioned in the following Microsoft Knowledge Base article:
837311 FIX: You cannot import data from a text file that is greater than 32 MB by using the BCP utility
Note You can use this hotfix file to resolve this problem even if the problem occurs on a computer that is not running Microsoft Windows XP.

WORKAROUND

To work around this problem, use the BULK INSERT Transact-SQL statement to import data from a text file that is larger than 32 MB to a table. To do this, follow these steps:
  1. Start SQL Query Analyzer.
  2. On the File menu, click New.
  3. Click Blank Query Window, and then click OK.
  4. Paste the following code in the query window:
    USE BcpTestDB
    go
    BULK INSERT [BcpTestDB].[dbo].[BcpTestTable]
    FROM '<Source Folder>\LargeFile.txt'
    WITH
    (
     CODEPAGE=932,
     FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'
    )
    go
    Notes
    • <Source Folder> is a placeholder for the folder where the LargeFile.txt file is created.
    • Code page 932 is a code page for the Japanese language.
  5. On the Query menu, click Execute to run the query. The data from the LargeFile.txt file is imported to the BcpTestTable table successfully.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the problem

Note To reproduce the behavior, you must add the Japanese language locale by using Regional and Language Options in Control Panel. You must then create a database table in an instance of Microsoft SQL Server, and then you must use the bcp utility to import a text file.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
177561 How to add and enable additional languages in Windows
  1. Use Regional and Language Options in Control Panel to add the Japanese language locale on the computer.
  2. Start SQL Query Analyzer.
  3. On the File menu, click New. The New dialog box appears.
  4. Click Blank Query Window, and then click OK.
  5. Paste the following code in the query window:
    CREATE DATABASE BcpTestDB
    COLLATE JAPANESE_CI_AS
    go
    USE BcpTestDB
    go
    CREATE TABLE [dbo].[BcpTestTable] (
    col1 int NULL ,
    col2 char(10) COLLATE Japanese_CI_AS NULL ,
    col3 char(10) COLLATE Japanese_CI_AS NULL ,
    col4 char(10) COLLATE Japanese_CI_AS NULL ,
    col5 char(10) COLLATE Japanese_CI_AS NULL ,
    col6 char(10) COLLATE Japanese_CI_AS NULL ,
    col7 char(10) COLLATE Japanese_CI_AS NULL ,
    col8 char(10) COLLATE Japanese_CI_AS NULL ,
    col9 char(10) COLLATE Japanese_CI_AS NULL ,
    col10 char(10) COLLATE Japanese_CI_AS NULL ,
    col11 char(10) COLLATE Japanese_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    This code creates the BcpTestDB database and the BcpTestTable table.
  6. On the Query menu, click Execute to run the query. The BcpTestDB database and the BcpTestTable table are created.
  7. Create a text file that is larger than 32 MB and that contains the information that uses the DBCS, and then save the file as LargeFile.txt.
  8. Click Start, click Run, type cmd, and then click OK.
  9. At the command prompt, type the following, and then press ENTER:
    bcp BcpTestDB.dbo.BcpTestTable in Source Folder\LargeFile.txt -T -c -S(local) -C932 -e Destination Folder\LargeFileError.txt
    Notes
    • The C switch option in the bcp utility is the code page specifier.
    • 932 is a code page for the Japanese language.
    • Destination Folder is a placeholder for the folder where you create the LargeFileError.txt error file.
You may notice that the data from the LargeFile.txt file is not imported to the BcpTestTable table. You may also notice error text that is generated in the LargeFileError.txt file in the Destination Folder folder.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
bcp Utility
http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx

BULK INSERT
http://msdn2.microsoft.com/en-us/library/aa225968(SQL.80).aspx

Collations
http://msdn2.microsoft.com/en-us/library/aa214408(SQL.80).aspx

Properties

Article ID: 883716 - Last Review: March 14, 2007 - Revision: 1.5
APPLIES TO
  • Microsoft Data Access Components 2.8, when used with:
    • the operating system: Microsoft Windows XP
Keywords: 
kbdatabase kbfix kblocalization kbimport kbcode kbsqlclient kbmdac280sp1fix kbqfe KB883716

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com