Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  Transactional Repl w/ Auto managed identity ranges

Author  Topic  alekb Starting Member 1 Post Posted - 2002-11-04 : 12:15:39 I have a queued transactional replication setup between two SQL Server 2000 SP2 servers. Since records may be inserted either at the publisher or the subscriber, I'm using automatically managed identity ranges for the tables that have identity fields. In this mode SQL Server is supposed to automatically assign and maintain an identity range for each the publisher and the subscriber. Let's say I choose range size of 1000 for both the publisher and the subscriber. When I set up the replication initally, the publisher gets the 0-1000 range, and the subscriber gets the 1001-2000 range. Let's say I insert two records at the subscriber which get 1001 and 1002 for their identity field. These records get replicated to the publisher. Everything works fine so far. I run into problems if I have to drop and recreate the publication for some reason (i.e. to add another table to the publication or modify structure of some table in the publication). What happens is the publisher and subscriber get the same identity ranges as before, and when I try to insert a record at the subscriber it tries to use the same value for the identity field (1001), which of course fails since a record with that value already exists. It would seem that SQL Server is supposed to manage the ranges in a way that would not cause such problems. Has anyone experienced this before? How did you solve it? The only workaround I see is to manually set the identity seed value on the subscriber after republishing. Of course, this is cumbersome in an environment with many tables and subscribers. I wrote a script to reproduce the problem. It's too big to include here, but let me know if you'd like to see it. Please let me know any thoughts you might have! Thanks, Alek