Board index MS SQL Server

Merge replication BUG with identity fields

Merge replication BUG with identity fields

Post by Rick Sawtel » Thu, 18 Feb 1999 04:00:00



You should NOT replicate the IDENTITY property.  Try using the ROWGUIDCOL instead and set the Default property to NEWID().

This should take care of your problem.

--
Rick Sawtell MCP, MCT, MCSD

10 Million Lemmings Can't Be Wrong


    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

 
 
 
Top

1. Merge replication BUG with identity fields

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 Dickey - 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

2. Run-time error 20000 ?

3. Merge Replication has duplicated IDENTITY field values!

4. 2 Apps same pdoxusers.net file?

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

6. Installing SQL Server 7.0 on Windows 2000 Advanced Server

7. merge replication identity conflict madness!!

8. Report Smith a nightmare !!!!!!!!!!

9. merge replication conflict and identity range problem

10. Identities and Merge Replication on SQL 2000

11. Merge Replication and Identity Problems on Subscribers

12. problem with merge replication and identity column

13. How to Partition Identity columns for Merge Replication?


All times are UTC
Board index