INF: How to Set Up Replication on Tables with an Identity Column

ID: Q190690

The information in this article applies to:

SUMMARY

Replication does not transfer the IDENTITY property of the schema at the subscriber. Because the data at the publisher is generated by the identity column, there is no need for this at the subscriber. However, in some rare cases, it is required to have an identical schema at the publisher and subscriber. In such cases, the distribution task will fail with an error if the table at the subscriber has an identity column.

This article provides the steps to set up replication on tables that have an identity column.


MORE INFORMATION

To replicate to a table at the subscriber that has an identity column, perform the following steps:

  1. Manually synchronize the tables. To do this, create the table with the identity column at the subscriber and use BCP /E to bulk copy the data in.


  2. Make sure the publication is defined to call a custom stored procedure for insert at the subscriber. For information on how to set up replication to use custom stored procedures, see the "Adding Stored Procedures for Insert, Update, and Delete" topic in the SQL Server Books Online.


  3. Create the custom stored procedure for insert at the subscriber. This procedure must SET IDENTITY_INSERT ON, do the insert, and then set the option back to off. A sample procedure will look like the following:
    
      create proc cp_insert_table @c1 datatype, @c2 datatype
      /* The columns will be passed in order. */ 
      as
      begin
         set identity_insert <tablename> on
         insert tablename values (@c1, @c2)
         set identity_insert <tablename> off
      end


  4. Subscribe to this table with either the manual synchronization or no synchronization option. Then subsequent transactions can be replicated to the subscriber successfully.


NOTE: If any new records are inserted at the subscriber, the new records will get the identity value and increment it. Subsequently, the distribution task may fail on an insert, giving a "duplicate key" error. This is because the insert from the distribution task is not aware of the insert that took place at the subscriber. Hence, the subscriber table must logically be read-only.

Additional query words: repl ident col field dist sync synch 


Keywords          : SSrvRep 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbhowto kbinfo

Last Reviewed: April 15, 1999