FIX: Bulk Insert May Give Extra Characters When Converting Text from Multi-byte to Single-byte Codepage

Article translations Article translations
Article ID: 278022 - View products that this article applies to.
This article was previously published under Q278022
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 57883 (SQLBUG_70)
Expand all | Collapse all

On This Page

Symptoms

Data that is loaded into a table may contain extra characters if all the following conditions are true:
  • The data is loaded with the BULK INSERT syntax.
  • The data includes multi-byte characters.
  • The data is extracted from a multi-byte code page server.
  • The data is loaded into a single-byte code page server.

Cause

When data is extracted from a multi-byte code page to a text file, any actual data that requires more than one byte for storage consumes extra space in the text file. The BULK INSERT statement correctly translates the data back to the correct representation, but then appends the "extra" characters that were at the end of the field.

For example, in SQL Server a field with a length of 150 characters that has 10 characters that take two bytes each to store, results in a text field length of 160. When that data is loaded into a SQL Server server with a single-byte code page, the BULK INSERT statement correctly translates the bytes back to the original characters, but then appends the extra ten.

Workaround

Use BCP.exe to load the data.

Status

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
199819 INF: SQL Server 7.0 BCP and Code Page Conversion

Properties

Article ID: 278022 - Last Review: November 2, 2013 - Revision: 3.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB278022

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