BUG: DTS: Table Transfers Incorrectly If Using Char or Varchar Larger Than 4000 Characters

• Microsoft SQL Server version 7.0

SYMPTOMS

Data in columns of a table with datatypes of char or varchar larger than 4000 characters will not be copied when you use the "TRANSFER OBJECTS AND DATA BETWEEN SQL SERVER 7.0 DATABASES" option in the Data Transformation Services (DTS) Export Wizard.

However, when you use the "COPY TABLES(S) FROM THE SOURCE DATABASE" option, the data is transferred correctly.

To determine if this situation is present, here are some sample errors likely to be seen in the DTS Transfer Log:

::Error:: In file in statement on line 0 Transfer Status: Transferring Data: (Table '[dbo].[bugtest]') [Microsoft][ODBC SQL Server Driver]Invalid precision value

::Error:: In file in statement on line 0 Transfer Status: Transferring Data: (Table '[dbo].[bugtest]') [Microsoft][ODBC SQL Server Driver]Function sequence error

::Error:: In file in statement on line 0 Transfer Status: Transferring Data: (Table '[dbo].[bugtest]') [Microsoft][ODBC SQL Server Driver]Not enough columns bound

CAUSE

The "TRANSFER OBJECTS AND DATA BETWEEN SQL SERVER 7.0 DATABASES" option uses Distributed Management Objects (DMO) data transfer, which treats datatypes char and varchar the same as Nchar and Nvarchar.

WORKAROUND

Transfer the objects and then BCP the data out from the source tables, and then into the destination tables.

Another option is to continue to use the TRANSFER OBJECTS AND DATA BETWEEN SQL SERVER 7.0 DATABASES option for the objects and then run the Data Transformation Services (DTS) wizard again using the "COPY TABLES" option to obtain the data. The copy tables option works because it does not use DMO and thus transfers the data correctly.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : SSrvDMO SSrvTrans kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug