Merge Replication has duplicated IDENTITY field values!

Merge Replication has duplicated IDENTITY field values!

Post by Michael Bredbu » Thu, 24 Jan 2002 00:30:28

I did not think it possible, but we have a number of records that have
the same value for the IDENTITY column in tables, which are
I do not understand how this has happened because the situation should
have been treated as a conflict, which I have seen before, resulting
in data-loss.

Each table is defined with an IDENTITY column set to be
auto-incrementing with a default seed and increment of 1,1
The server is SQL 7 (SP3) and the laptops with the replicated data are
running MSDE (SP3), Windows 2000 (SP2), MDAC 6.1, anonymous merge
replication over a RAS server via modem.
The replicated tables have dynamic filters and are set to use the
default resolver.
The records which have been duplicated have come from the Laptops.
The 'NOT FOR REPLICATION' option has NOT been set on the subscribers
(Laptops)  and the IDENTITY seed and increment has NOT been changed
from the default of 1,1.
I would say that there is nothing out of the ordinary about our setup

Apart from this duplication, everthing else appears to be working

What is ironic is that for us, this situation, although faulty, is
actually better than having conflicts and data-loss!

The structure of 1 of the tables containing records with identical
IDENTITY column values:

CREATE TABLE [dbo].[marketing_matrix] (
        [jcbf_id] [int] IDENTITY (1, 1) NOT NULL ,
        [account_no] [varchar] (6) NULL ,
        [xyear] [nvarchar] (4) NULL ,
        [xmonth] [varchar] (3) NULL ,
        [equipment] [varchar] (50) NULL ,
        [unit_value] [varchar] (2) NULL ,
        [COMMENTS] [varchar] (1000) NULL ,
        [Returned_On] [datetime] NULL ,
        [action_date] [datetime] NULL ,
        [action_by] [varchar] (4) NULL ,
        [complete_date] [datetime] NULL ,
        [source] [varchar] (32) NULL ,
        [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL

Any takers?

Mike Bredbury.


1. Attempt to insert duplicate identity values in table (has identity and key fields)

I have set up my tables to have auto-generated unique IDs for each record.
The ID field is set to "identity" and has a unique constraint on it making
it a key field.

I create new records from an ACCESS 2000 front end. It was working fine
until yesterday when an operator shown me an error message to the effect ;
"tblxxx , attempt to insert duplicate key (or identity) values)..... error

The record would not be inserted. I went into SQL server (6.5) enterprise
manager and looked at the table indexes. I clicked "rebuild" and under
distribution "update".  After this new records would be inserted.

I  do not know why the problem occurs in the first place. I also do not know
why I was able to clear the problem by manually rebuilding the indexes. Do I
need to have a procedure to automatically check the indexes?

Any help would be appreciated. Thanks.

2. Is there any newsgroup out there for FP?

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

4. NT MCSE + I Contractor - GURU, Contract

5. Merge replication resetting identity seed values

6. MDAC on Windows ME

7. Duplicate value in IDENTITY field

8. Timeout Expired

9. Duplicate values for IDENTITY field

10. Duplicate Identity Field Values

11. Merge replication BUG with identity fields

12. Getting identity field value from replication database