BUG: DTS: Table Transfers Incorrectly If Using Char or Varchar Larger Than 4000 Characters (244537)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/16/2002 |
|---|
| Keywords: | kbBug KB244537 |
|---|
|