I am using a merge replication setup for our ERP system and I encountered this problem:

While creating a new entry on a table and with the last ID number given was 25, then new entry got the ID number 40000+ (I don't quite remember the exact number). I found a way to solve it with this query:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

but I wasn't sure if using this would be ok with the replication. I know that replication uses an ID pool range for each system connected to the publication. Anyway after restarting the System the ID seed was fixed.

What I want to ask is if there is a safe way to change the ID range for a whole table if needed while using replication.

You need to turn no 'NOT FOR REPLICATION' on the field, then the target will take the identity field from the source table rather than trying to generate its own

this can be done by:

alter table dbo.testtable
alter column [id] add NOT FOR REPLICATION

NOTE, I'm not sure what doing this while replication is enabled will do, if you do enable it while replication is on, ensure your replicate schema changes setting is on for that publication

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.