by
You need to do some extra work to replicate IDENTITY columns.
First you need to set the NOT FOR REPLICATION attribute on the IDENTITY columns.
You must also set the IDENTITY SEED values in the subscriber table after the snapshot has been delivered when the replication is initialized. You can do this using DBCC CHECKIDENT
I have a couple short articles on my web site describing replication issues including using IDENTITY.
I have 2 systems (4 servers each) working at customer sites using Merge and Transactional replication with IDENTITY columns as the primary keys.
The SEED value is modified on a subscriber table during the initialization phase even if NOT FOR REPLICATION is in place.
Also beware that Enterprise Manager will loose the NOT FOR REPLICTION setting if it needs to make a new table during a schema change that you perform in table design view.
Chris*ey - MCSE
www.cdickey.com/sql7/
We are merge replicating two tables who have identity fields.
The publication table has an identity seed of 1 and an increment of 10.
The subscribing table has an identity seed of 2 and an increment of 10.
After the first replication everything works as planned, but after the second replication, SQL server changes the identity seed of the subscribing table to 1.
In the snapshot properties of the publication, we indicate that if a table with the same name exists at the subscriber SQL should delete all records (using truncate)
We do not replicate indexes or keys.
In the subscription we indicate that sql should not initialise the shema and data, in order to preserve the difference in identity seeds.
I think we filled in all the options correctly ?!
Michael Bentein