Microsoft KB Archive/922648

= The binary zero (0x00) data and the successive data in a character column is truncated or is replaced with spaces (0x20) when you use the SQL Server Import and Export Wizard to transfer a table in SQL Server 2005 Integration Services =

Article ID: 922648

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Express Edition with Advanced Services
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-





SYMPTOMS
Consider the following scenario. You use Microsoft SQL Server 2005 Integration Services (SSIS) to transfer a SQL Server table from a SQL Server source to a SQL Server destination. Binary zero (0x00) data is stored in a character column. Binary zero (0x00) data is also known as a null terminator. In this scenario, the binary zero (0x00) data and the successive data in the character column is truncated. Alternatively, the binary zero (0x00) data and the successive data in the character column is replaced with spaces (0x20).

This behavior occurs when the following conditions are true:
 * You use the SQL Server Import and Export Wizard to transfer the table.
 * The table contains a column that is defined as the char data type or as the varchar data type.

If the column is defined as the char data type, the binary zero (0x00) data and the successive data is replaced with spaces (0x20). If the column is defined as the varchar data type, the binary zero (0x00) data and the successive data is truncated.

Note Unicode character data types may also exhibit unexpected behavior during the transfer of embedded binary zero data. When you transfer a table that contains a column that is defined as the nchar data type or as the nvarchar data type, the behavior is slightly different. In this case, the binary data 0x0000 is truncated or is replaced with 0x0020.



CAUSE
This behavior occurs because Integration Services does not support storing embedded binary zero data in a character column.



RESOLUTION
To resolve this behavior, you must store binary data in a column that is defined as the varbinary data type.



STATUS
This behavior is by design.



MORE INFORMATION
This behavior differs from the behavior of the bcp utility and of the BULK INSERT statement. The bcp utility and the BULK INSERT statement maintain the embedded binary zero data.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine kbsql2005ssis KB922648

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.