Microsoft KB Archive/244537

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

Article ID: 244537

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q244537



BUG #: 54768 (SQLBUG_70)



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 7.0.

Keywords: kbbug kbpending KB244537

-

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

© Microsoft Corporation. All rights reserved.