Merge Replication and Identity Problems on Subscribers

Merge Replication and Identity Problems on Subscribers

Post by Iain Wardl » Thu, 10 May 2001 00:20:48

Hi all,

I am using SQL server 7.0 sp2.  What I am doing is Merge replication of a
database with one subscriber.  The database has an Identity column on one of
the tables, the publisher and subscriber have different range values for
inserts with the 'Not For Replication' option set. eg. Publisher 1-50000,
Subscriber 50001-10000, this works fine with the insert on either side
updating the correct identity values.  However, when I use a update the

the publisher it works fine but the subscriber always returns 0.  I have had
a quick look at the Triggers placed on each table but they are exactly the
same.  Anyone else ever come across this problem?? And perhaps have a


Iain Wardle


Merge Replication and Identity Problems on Subscribers

Post by Michael Hote » Thu, 10 May 2001 07:21:55

It's a known issue between triggers and identities.  I can't remember if it
was fixed in a 7.0 service pack or if the fix went into SQL Server 2000.

Please post all replies to newsgroups unless specifically stated otherwise.

SQL Server MVP
"Aim high. You can never achieve more than you aspire to." - Al Ries


1. Trouble using identity ranges for subscribers of a merge replication

After the initial snapshot with the "DROP and re-create" option,

I remove the subscriber from the subscribers-list to edit the subscribtion
tables to have its specific identity range.

Then, I put the subscriber back on the subscribers-list and change the
snapshot option to
"Delete data in the existing table that matches the row filter". I also
leave the
"Include declared referential integrity on the primary keys" SELECTED!

When I run the re-sync, everyting is fine except the subscriptions table now
miss all the FK-relations, which were there before.

It looks like, that the "Include referential integrity" option is silently
connected to the
"DROP the existing table and re-create it" option.

But when I would use this option, I would loose the identity range settings.

A real dilemma!

So, currently I have to manually re-create the lost FK-relations.

Am I doing smth. wrong, or is it just the way it has to be?

Many thanks in advance.

Erd?l Biramen MCP+Internet, MCSE, MCSD, MCDBA, MCT

2. TPC-B or other Benchmarking utils??

3. Merge replication: state after problems at a subscriber

4. Setting the Paradox level parameter

5. merge replication conflict and identity range problem

6. Create indexes question

7. problem with merge replication and identity column

8. Please recommend a DB server for this...

9. Merge replication with identity value ranges using not for replication option

10. Merge Replication - subscriber not uploading

11. Merge replication deleting records on subscriber after sync

12. Merge replication, without replicate changes on the subscriber

13. Central subscriber and merge replication