Article ID: 257425 - View products that this article applies to.
This article was previously published under Q257425
BUG #: 53859 (SQLBUG_70)
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:
The Object Transfer feature uses SQL Distributed Management Objects (SQL-DMO) to perform the transfer. DMO is not designed to transfer more than 64 KB of BLOB data per field.
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:
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:
232570For more information, contact your primary support provider.
(http://support.microsoft.com/kb/232570/ )INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
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:
Note that the GUI reports this message even if rows are truncated:
::Error:: Transfer Status: Transferring Data: (Table '[dbo].[bigtext]') Data truncation occurred in table [dbo].[bigtext], column 2.
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:
Successfully copied 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:
Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server.
[SQL-DMO]The Bulk Copy execution failed.