Replication Fails With Violation of PRIMARY KEY constraint

[Pitt@discussions.microsoft.com Michael Pitt]

Hi

I have a SQL 2000 database with is roughly 4gb in size. It is published and
replicates to 3 subscribers. This has worked fine for months, until last
week when one of the subscribers starting failing with the error message
"Violation of PRIMARY KEY constraint 'PK_CustomerInformation'. Cannot insert
duplicate key in object 'CustomerInformation'.

I have tried deleting the subscriber and recreating it so replication begins
again for that node from scratch. I have also applied the SP3 security
rollup hotfix, but this has not made a difference. The other two subscribers
are still working fine.

Has anyone seen anything like this before?

Cheers,
Mike.

[Paul Ibison]

Michael,
what type of replication are you using? Did you use
nosync initializations? Do you have range management
on 'PK_CustomerInformation' - automatic or manual?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Nitin]

It shouldn't be that hard. Just make sure that customer table doesn't have a
duplicate record in it. If so, delete it first and then restart the dist
agent for that subscriber.

As you said, you re-started the replication with this subsriber. Did you
modify the insert procedures at any point? If so, look at the business rule
in the insert procedures and may be there is something that may shed some
liught.

-Nitn

[Khooseeraj Moloye]

Mike,

This problem has been reported by many before you! It is more a problem of
data management than replication itself. You have a record created at one
subscriber that matches another record with the same primary already
existing at the Publisher. Here the replication process is merely notifying
you of a row conflict between two databases.
If your table has an identity column which is defined as the primary key,
you may have to reseed the table so that records with same identity are not
generated across several subscribers (for existing conflicts they should be
resolved to recover the data).
My advice: check what your primary key is, then check the conflicting data
against corresponding data already existing at the Publisher/Subscriber.
Please give some more information.

Rgds,
Raj Moloye

[Michael Pitt]

Hi

Thanks for your help. I am using transactional replication. The
CustomerInformation has an identity field as its primary key, and this is set
to "not for replication".

On the other two subscribers, the schema does not show this field as an
identity field. On the third subscriber, which was working fine, whenever I
reinitialize synchronisation and say that I want the snapshot agent to
replicate the initial schema and data, the third subscriber gets the full
schema (with active identity fields) which is what is causing the problem.

Any other thoughts would be appreciated.

Thanks,
Mike.

[Khooseeraj Moloye]

Hi Michael,

I don't know if I can help you much as I am more versed towards Merge
Replication nowadays, and haven't used Transactional replication for some
time now.
However, the following should be borne in mind:
1. Among your three subscriptions, the table schema for that one table (and
maybe others as well) you mentioned is not the same on the third subscriber
compared to the other two. This shows some inconsistency in managing the
schema among those subscribers. Only you can tell.
2. Are changes allowed at the Subscriber? If yes, you should have either
Immediate Updating or Queued Updating enabled. Check what type is yours. The
Identity property of the table is not copied to the subscriber unless these
options are enabled.
3. How is the table created at the subscribers. You can know this by looking
in the Snapshot folder of the table article properties in the publication.
Here you should have four options to select from in the Name Conflict
section.
4. If Immediate Updating or Queued Updating options (see 2 above) are not
enabled and your subscriber has the identity property enabled for that one
table, most probably the table has been created manually with the Identity
property enabled.

Finally, there are so many possibilities... please use the above to find and
read some related stuff in Books Online and report your exact implementation
(and probably some history!!)

Hope the above helps.
Regards,
Raj

"Michael Pitt" <Micha...@discussions.microsoft.com> wrote in message
news:4D65F81F-96D1-486E...@microsoft.com...