Home Forums SQL Server 2005 SS2K5 Replication transactional replication error in sql 2000 server: violation of primary key RE: transactional replication error in sql 2000 server: violation of primary key

  • let's do it step by step.

    Replication tries to copy a record from publisher to subscriber. If it tells you a PK violation one of the records to be transferred already exists on the subscriber. So you have too find the value on the subscriber!

    I'm not sure of the error messages in SQL 2000 but I think it gives you at least the name of the stored procedure that failed. This way you know on which table the error occured (you know this too by the name of the primary key named in the error message).

    How to find the primary key value?

    Have a look again at your error message. PK violations usually contain the value that could not be inserted.

    Another possibility: the error message should contain the xact_seq_nr or something similar. With this you can use the browsereplcmds-command on the distribution database to get the exact command that raises the error. This command should contain the primary key value.

    Example:

    exec sp_browsereplcmds @xact_seqno_start = '0x0003BB0E000001DF000600000000', @xact_seqno_end = '0x0003BB0E000001DF000600000000'

    Next possibility: Use the profiler. As you already know the procedure that the replication executes create a trace that shows you the executions of this procedure. You should get the exact input parameters here.

    If you finally found the PK value, what you do now?

    As always, it depends.

    If the table on the subscriber is only for read operations and is never changed just delete the record from the subscriber. Replication will insert it with all data of the publisher.

    If you change the records on the subscriber you cannot just delete it as the changes done on the subscriber get lost. You could store the record on the subscriber, then delete it. After insert of the replication you could manually update it with the saved values.

    Hope that helps.