Article ID: 278022
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q278022
BUG #: 57883 (SQLBUG_70)
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
Additional query words: "Bulk Insert" codepage text dualbyte doublebyte garbage extra multiple
Keywords: kbbug kbfix KB278022
