Post reply

Violation of Primary Key

  • August 8, 2006 at 5:32 am

    #173760

    I have SQL 2005 Ent setup with peer to peer replication. I can insert records into each database on the primary server and the secondary server. My problem is that if I enter a record into table X on the primary server, I need to wait a nubmer of minutes before I can enter records in table X on the secondary server. If I try to insert the record immediately on the secondary server I get the following error:

    "Violation of Primary Key contraint 'XXXXXX'. Cannot insert duplicate key in object 'XXXXXX'

    The records entered into the primary server appear in the seconday server with seconds. If I wait about 5 minutes, then i can insert a record in the secondary which appear in the primary table, but again I need to wait before I can enter more records in table X on the primary server.

    I am by no means a DBA. I know enough about SQL to be dangerous. Is there any way that I can change the time before I can insert records after an update between the peers?

    When I have had this issue, I have used DBCC checkident ('X', reseed) and then I am able to insert records immediately, but this is not an option as this would need to be included in the code of our website. Any help would be appreciated.

  • August 11, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • August 17, 2006 at 6:25 am

    #655423

    If you are coding via T-SQL, you can use the "WAITFOR DELAY":

    Example pulled from BOL:

    WAITFOR DELAY '00:00:02'

    SELECT EmployeeID FROM AdventureWorks.HumanResources.Employee;

    SYNTAX:

    WAITFOR

    {

    DELAY 'time_to_pass'

    | TIME 'time_to_execute'

    | ( receive_statement ) [ , TIMEOUT timeout ]

    }

    Thanks...Michelle

  • August 25, 2006 at 9:06 am

    #656855

    It sounds to me like you may not be using an identity as a primary key. If so, you need to use identity range management to ensure prevent this error message from occuring. You can find a good article online called "The Identity Crisis". Two possible alternatives for identity range management for two servers would be using an increment of 2 and reseeding each db so that db1 is odd and db2 is even. You could also use a dual primary key with an identity as half and a default server id for the other. Hope this helps.

    Brent

Viewing 4 posts - 1 through 3 (of 3 total)

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