Distribution Agent cannot insert duplicate keys

Question

Distribution Agent cannot insert duplicate keys

Ed7

SSCrazy Eights

Points: 8519
More actions
May 1, 2009 at 2:53 pm

#204002

Hello room,
I need to setup native transaction replication that will prevent my subscriber data be deleted when someone clean up the publisher databases three year from now.
Enclosed are the configuration environments:
1.Publishers (SQL Server 2000 SP3)
2.A remote distributor (SQL Server 2008)
3.A single subscriber (SQL Server 2008)
Next, I updated my publication’s article with the following scripts
EXECUTE sp_changearticle ‘xxxx_site’, ‘t_EnergyDataDaily’, ‘del_cmd’, ‘NONE’;
Then, I run the initial snapshot and it was completed with no errors.
The transactions replication was running for two days.
On the third day, the distribution agents purged an errors:
•Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000858C00000B29007300000000, Command ID: 1)
Error messages:
• Violation of PRIMARY KEY constraint 'aaaaat_EnergyDataDaily_PK'. Cannot insert duplicate key in object 'dbo.t_EnergyDataDaily'. (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
• Violation of PRIMARY KEY constraint 'aaaaat_EnergyDataDaily_PK'. Cannot insert duplicate key in object 'dbo.t_EnergyDataDaily'. (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Can anyone help and advice?
Thanks,
Edwin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 1

It seems to be that data that distribution agent is trying to insert at subscriber already exists. This could have happened either some one possibly could have inserted records manually at subscriber.

To figure out records for which replication is having issues, please do the following.

Get the distinct xact_seqno from MSrepl_errors table.

step 1 )

select distinct xact_seqno from distribution.dbo.MSrepl_errors

where xact_seqno is not null and time >= 'datetime'

DateTime value should be greater than or equal to since when the started happening.

step 2)

Extract each single value and pass it in this proc

exec distribution.dbo.sp_browsereplcmds @xact_seqno_start = 'extracted value',@xact_seqno_end ='extracted value'

This proc gives bunch of results, in this look for command column value which is in readble format.

This is the command it is trying to execute on subscriber and failing for primary key constraint and try deleting this record on subscriber.

Upon deleting record on subscriber replication should continue, if this error happens there could be some other duplicate records, continue this process till all duplicate records are deleted at subscriber.

Please let me know how it goes.

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 2

Hi Murthy,

Would you please give me some advice on my current database replication?

Did my database replication implement it unusual or incorrect?

By changing or updating the publication articles before the initial snapshot.

If you answer is yes.

Can you advise any methods to implement in order to meet the business’ requirements?

I noticed that I implemented with native transaction database replication without changing or altering the publication's articles, I won’t get those errors.

Thanks,

Edwin

noeld SSC Guru Points: 96590 More actions · Answer 3

If you don't allow deletes and somehow you are running updates on Primary Keys or unique indexes columns, these get replicated as a Delete+Insert. Because the deletes were not sent your inserts will bomb out if this happens. Been there done that.

* Noel

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 4

Hi Noel,

Would you recommend and advise any methods to prevent subscribers’ data be deleted with the native transaction database replications?

Thanks for your help and advice.

Edwin

noeld SSC Guru Points: 96590 More actions · Answer 5

I personally use instead-of triggers marked as NFR. It has worked very well for me.

* Noel

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 6

Hi Noel,

I have not implemented one yet.

Would you mind share with me, pls send to tjedwin@gmail.com?

Thanks,

Edwin

noeld SSC Guru Points: 96590 More actions · Answer 7

Edwin (5/4/2009)
Hi Noel,
I have not implemented one yet.
Would you mind share with me, pls send to tjedwin@gmail.com?
Thanks,
Edwin

Here is an example:

CREATE TRIGGER [TR_Yourtablename_instead_of_iud] ON [Yourtablename]

INSTEAD OF INSERT,UPDATE,DELETE NOT FOR REPLICATION AS

BEGIN

SET NOCOUNT ON;

RAISERROR ('Data can only be changed by replication ',16,1 )

END

* Noel

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 8

Hi Edwin,

I was not having access to system yesterday as I was away with personal work hence cann't reply back.

Neol has suggested a solution, hope that worked, if you still have any issues, pls post, we will take it from there.