Queued Updating Considerations

When using queued updating, consider the following:

• Queued updating is supported only with Subscribers running SQL Server 2000.
  
  
• If you create two or more articles on the same table, and then create subscriptions to those articles in the same Subscriber database, the following restrictions apply:
  • If multiple articles based on the same table are in a single publication enabled for queued updating, you cannot create a queued updating subscription to this publication.
    
    
  • If multiple articles based on the same table are in different publications and you want to create subscriptions to all publications in the same database, only one of the subscriptions can be queued updating.
• The publication access list (PAL) must include at least one SQL Server Authentication account.
  
  
• Subscribers using immediate updating or queued updating cannot republish replicated data at the Subscriber.
  
  
• If a transaction at the Subscriber involves multiple databases, compensating commands are generated only for the updates affecting the subscription database in case of a conflict.
  
  
• Tables included in a merge publication cannot also be published as part of a snapshot or transactional publication that allows queued updating subscriptions.

Modifying Data at the Subscriber

When modifying published data at the Subscriber, consider the following:

• The Subscriber cannot update or insert text or image values because they cannot be read from the inserted or deleted tables inside the trigger. Similarly, the Subscriber cannot update or insert text or image values using WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher. Instead, you could partition the text and image columns into a separate table and modify the two tables within a transaction. Use merge replication to synchronize these values. You cannot be assured there are no conflicts because the update of the text or image table can occur if the data is not well partitioned.
  
  
• INSERT statements used to add rows of data to a table must include a column list.
  
  
• It is recommended that Subscriber tables have at least a unique index and preferably a primary key for snapshot replication. This is required for transactional replication.
  
  
• Although snapshot replication without immediate updating does not require the use of primary keys in a table, snapshot replication or transactional replication with an immediate updating subscription requires you to use primary keys on publishing tables. Although snapshot replication does not require the use of primary keys in a table, queued updating does require the use of primary keys.
  
  
• Updates made to primary key columns are not recommended when using queued updating because the primary key is used as a record locator for all queries. When the conflict resolution policy is set to Subscriber Wins, updates to primary keys should be made with caution. If updates to the primary key are made at both the Publisher and at the Subscriber, the result will be two rows with different primary keys.

For example, if a row has a value of 'Bill' in the primary key column, and that value is updated to be 'William' at the Publisher and to 'Will' at the Subscriber, both the publication database and the subscription database will end up with two rows (one with the primary key 'William', and the other with the primary key of 'Will'). It is recommended to restrict primary key updates to a single site (for example, you could restrict primary key updates by adding an update trigger at the Subscriber that prevents updates to columns participating in the primary key. The trigger could be added to any necessary Subscribers by using script execution before or after applying the initial snapshot). 

• Updates to unique keys (including primary keys) that generate duplicates (for example, an update of the form UPDATE <column> SET <column> =<column>+1) are not allowed and will be rejected because of a uniqueness violation. This is because set updates made at the Subscriber are propagated by replication as individual UPDATE statements for each row affected.
  
  
• If the Subscriber database is partitioned horizontally and there are rows in the partition that exist at the Subscriber but not at the Publisher, the Subscriber cannot update the pre-existing rows. Attempting to update these rows returns an error. The rows should be deleted from the table and added again.

Manual Initial Synchronization of a Queued Updating Subscription

If you subscribe to a transactional publication that allows queued updating subscriptions, but you do not have the subscription initialized automatically by SQL Server, all of the objects (custom stored procedures, change tracking triggers, and conflict table) will not be created. You will need to create them manually with the following steps:

1. Script the creation of the table at the Publisher, and using that script, create the table in the subscription database. If you create the script manually, include the primary key constraint.
   
   
2. In the publication database, execute the following stored procedures:
   • sp_scriptinsproc (specify the @article_id parameter).
     
     
   • sp_scriptxupdproc (specify the @article_id parameter).
     
     
   • sp_scriptxdelproc (specify the @article_id parameter).

These will generate scripts for custom stored procedures to be applied to the subscription database. Execute these scripts in the subscription database. The article ID value can be obtained by executing sp_helparticle.

3.  In the publication database, execute the following system stored procedure:
   • sp_makeconflicttable (specify the @publication and @article parameters).

This stored procedure returns 0 if successful and 1 if not successful. This generates a script for the conflict table for the given article. Execute this script in the subscription database.

4. At the Subscriber, execute the following system stored procedure:
   • sp_addsynctriggers. For more information, see sp_addsynctriggers.