Microsoft KB Archive/190690

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

Article ID: 190690

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q190690



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:

 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. 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.  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 on        insert tablename values (@c1, @c2) set identity_insert off end

 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: kbhowto kbinfo KB190690

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.