StarQuest: keeping data in motion

 

StarQuest Technical Documents

Error Replicating Text or Image Data to SQL Server

Last Update: 03 September 2005
Product: StarQuest Data Replicator
Version: 3.x
Article ID: SQV00DR013

Abstract

When replicating text or image columns containing null or zero-length data to SQL Server destination tables using the Replication Option native-loader function, you may experience one of two problems, depending on the SQL Server ODBC Driver version:

  1. The replication subscription will fail with the error message:

Bulk-copy commit failed (bcp_batch). ODBC message: SQLSTATE 37000, native error 4813, [Microsoft][ODBC SQL Server Driver][SQL Server]Expected the text length in data stream for bulk copy of text, ntext, or image data.

  1. Or, the number of rows actually replicated will not match the number of expected rows.

Solution

This problem can be resolved using one of the following methods:

  1. Change the Replication Option for the subscription to Insert using ODBC. This option can be found on the subscription Destination Tab.
  2. Re-order the columns replicated so that the text/image column containing null or zero-length data is the first column of the destination table. On the Columns Tab of the subscription, highlight the column and click the Move Selected Column up arrow until the column is in the first position. This workaround may not be suitable if the table contains more than one text or image column with null or zero-length values.
  3. Filter out any null or zero-length data by specifying a WHERE clause in the subscription Criteria Tab. An example would be WHERE <text_column> IS NOT NULL.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.