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.