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

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.

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