BUG: DTS May Return a Success Even When it Fails to Transfer Data (301383)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q301383
BUG #: 101719 (SQLBUG_70)

SYMPTOMS

The Transfer SQL Server Objects task of Data Transformation Services (DTS) may return a success even if it fails to transfer the character data into the destination database. This may occur when you try to transfer the character data into a column in which a character data type (char or varchar) is smaller than the column of the source table. If the data of the source table is larger than the column of the destination table DTS may output the following error message to the <dbowner>.<table>.BCP file on the script file directory:
#@ Row x, Column x: String data, right truncation @# xxx xxxx xxxxxx

WORKAROUND

To work around this problem you can either:
  • Use the Drop destination objects first option in the Transfer SQL Server Objects Task.

    -or-

  • Widen the column of the destination table so that it is larger than the column of the source table.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 7.0.

MORE INFORMATION

This problem also occurs when you use the Copy objects and data between SQL Server database option in the Data Transformation Services Import/Export Wizard.

Steps to Reproduce Behavior

  1. Execute the following SQL Server scripts to create a sample database:
    use master
go 
create database db1
go
create database db2
go
use db1
go
create table t1 (c1 int not null, c2 varchar(4))
go
insert into t1 values(1,'aaaa') 
go
use db2
go
create table t1 (c1 int not null, c2 varchar(3))
go
  2. Execute the Data Transformation Services Wizard.
  3. Click Next.
  4. Under the Choose a Data Source tab, select the following options, and then click Next:

    Microsoft OLE DB Provider for SQL Server
    Server: <Your Server>
    Use Windows NT authentication
    Database : db1

  5. Under the Choose a Destination tab, select the following options, and then click Next:

    Microsoft OLE DB Provider for SQL Server
    Server: <Your Server>
    Use Windows NT authentication
    Database : db2

  6. Under the Specify Table Copy or Query tab, select Transfer objects and data between SQL Server 7.0 databases, and then click Next. button.
  7. Under the Select Objects to Transfer tab, select the following options, and then click Next:

    Create destination objects (tables, views, stored procedures, constraints, and so forth)
    Include all dependent objects Copy Data Replace existing data Transfer all objects User default options

    NOTE: Do not select the Drop destination objects first check box.

  8. Save this DTS package, and then run the package immediately.
Modification Type:MajorLast Reviewed:6/22/2001
Keywords:kbbug KB301383
©2002 Microsoft Corporation. All rights reserved.