PRB: Bulk Operations into Tables Result in Rows Not Replicated in Merge Replication

When you use bulk operations such as the bcp command-line utility, BULK INSERT Transact-SQL statements, the bulk copy API, or SQL Server Data Transformation Services (DTS), to load data into tables that participate in merge replication, unless certain measures are taken, the inserted rows are not replicated to other servers.

Because these bulk operations are optimized for fast and efficient loads of large amounts of data, by default they do not fire triggers on the table receiving the inserted data. Because merge replication requires the use of system triggers on participating tables to track changes between the publisher and subscriber, if the triggers are not fired, merge replication cannot replicate the rows.

The workaround for this problem depends on the method you are using to bulk copy data and the version of SQL Server you are using:

• If you are using SQL Server DTS to insert rows, clear the Use Fast Load option (enabled by default) under the Options tab of the Data Transformation Task property sheet. However, note that the SQL Server 2000 Bulk Insert Task does not expose the ability to change the Use Fast Load option, only the Transform Data Task does.
  
  
• If you are using either the bcp command-line utility or the Transact-SQL BULK INSERT statement in SQL Server 2000, you must use the FIRE_TRIGGERS option. Note that the FIRE_TRIGGERS option is not available in SQL Server version 7.0. Because the FIRE_TRIGGERS option for bulk operations in SQL Server version 7.0 is not available, any bulk operation outside of DTS results in rows not being replicated to subscribers.
  
  
• When you use the bulk-copy API through ODBC, you must enable the FIRE_TRIGGERS option on the appropriate connection handle through a call to the bcp_control() function.
  
  
• When you use the IRowsetFastLoad OLE DB interface, specify the FIRE_TRIGGERS option for the SSPROP_FASTLOADOPTIONS property on the IOpenRowset interface.

Insert, delete, and update commands that are to be replicated in a merge replication publication are tracked by the merge agent when they are added to the MSmerge_contents and MSmerge_tombstone tables. Rows are added to these tables through system triggers created on tables during replication setup.

Bulk operations do not check table constraints on, or fire triggers at, the destination table. As a result, the merge replication system triggers are not fired. Because these triggers are not fired, the rows that are "touched" by bulk insert operations are not added to the MSmerge_contents table and therefore are not replicated to subscribers.

REFERENCES

For more information about the effects of trigger execution at the destination tables when the FIRE_TRIGGERS option is specified, see the "Using bcp and BULK INSERT" topic in SQL Server 2000 Books Online.