When replicating text or image columns that contain null or zero-length data to SQL Server destination tables using BCP, which is the native-loader function for SQL Server, you may experience one of the following problems, depending on the SQL Server ODBC Driver version:
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.
You can resolve this problem by using one of the following workarounds:
Change the Destination properties for the subscription to use Insert Using ODBC. This option appears in the Replication Options for snapshot subscriptions or as Initial Snapshot Replication Options for incremental subscriptions.
Re-order the columns that are replicated so that the text/image column that contains null or zero-length data is the first column of the destination table. On the Columns pane of the subscription properties, highlight the column and click the Move Selected Column up arrow until the column is in the first position. This workaround is not suitable if the table contains more than one text or image column with null or zero-length values.
Filter out any null or zero-length data by specifying a WHERE clause in the subscription Criteria properties. An example would be WHERE <text_column> IS NOT NULL.