FIX: DTS Object Transfer Does Not Transfer BLOB Data Greater Than 64 KB (257425)

SYMPTOMS

The Data Transformation Services (DTS) Object Transfer feature may not successfully transfer Binary Large Object (BLOB) data if there is more than 64 KB of data in any row in a text, ntext, or image column in the database DTS is transferring.

The symptoms vary, but may include:

Access violations or other exceptions.
SQL Server Enterprise Manager (SEM) may stop responding (hang).
The transfer of incorrect data.
The transferred data may be truncated.

WORKAROUND

Object Transfer is the option in the DTS Import/Export Wizard labeled Transfer objects and data between SQL Server 7.0 databases. To avoid the problem described in the "Symptoms" section, use the Copy table(s) from the source database option for tables with large amounts of text, ntext, or image data.

You may want to use Object Transfer to transfer the schema of the affected tables first, and then use the Copy table(s) from the source database option to move the data (Copy tables does not transfer indexes, constraints, permissions, and other schema details). To transfer schema only, clear the Copy data checkbox in the wizard before you use you Object Transfer to transfer the tables.

To identify the tables in the source database that have text, ntext, or image columns that this problem may affect, run the following query:

   USE <source database name>
   GO
   SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE DATA_TYPE IN ('text', 'ntext', 'image')

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

If the computer on which you are running Enterprise Manager (SEM) has SQL Server 7.0 Service Pack 1 or later installed, you should not experience access violations, SEM instances that stop responding, or other unexpected problems during a transfer of BLOB data that is larger than 64 KB. However, the DMO Transfer methods are still limited to a maximum BLOB size of 64 KB, so text, image, and ntext data larger than 64 KB truncates at 64000 bytes in the destination database. The file <server>.<destination db>.LOG in the transfer log directory reports each row truncation:

  ::Error::
  Transfer Status: Transferring Data: (Table '[dbo].[bigtext]')
    Data truncation occurred in table [dbo].[bigtext], column 2.

Note that the GUI reports this message even if rows are truncated:

Successfully copied objects from Microsoft SQL Server to Microsoft SQL Server.

The SQL Server 2000 DMO Transfer (or Object Transfer in DTS) transfers text up to 8 MB in size. If you try to transfer text that is larger than 8 MB, the DTS transfer fails with this error message:

Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server.

If you double-click the failed step in the Executing Package dialog box (Copy SQL Server Objects), this error message appears:

[SQL-DMO]The Bulk Copy execution failed.