Microsoft KB Archive/278022

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

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 &quot;extra&quot; 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.