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

• Microsoft SQL Server version 6.5

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.

Additional query words: repl ident col field dist sync synch

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