none
violation of PRIMARY KEY

    Question

  • Jobs run at 1 am , sometimes fail with following error.

    Violation of PRIMARY KEY constraint 'PK__xxxxx__300424B4'. Cannot insert duplicate key in object 'VW_xxxx . [SQLSTATE 23000] (Error 2627)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

    when I am reruning  7 am job run  successfully. Any advice will be great.

    Thanks
    Friday, September 14, 2007 10:49 PM

Answers

All replies

  • What type of column is your primary key?  How is it derived?  What other tasks run at 1:00 AM?

    Saturday, September 15, 2007 1:08 AM
    Moderator
  • check the tasks of this job. basically, this job is inserting rows to some tables and sometimes its trying to insert rows which already exists it gets Primary Key violation. Change the logic to If Exits not to insert the rows.

     

    Madhu

    Saturday, September 15, 2007 6:45 AM
    Moderator
  • Column Is INT, There is no other Task run at 1 am .
    Monday, September 17, 2007 2:43 AM
  • If Exits not to insert the rows : This logis already exits.
    Monday, September 17, 2007 2:45 AM
  • the Primarykey voilation may be occuring in some other table. Check for any trigger which insert into some other table from this table.

     

    Madhu

     

    Monday, September 17, 2007 8:32 AM
    Moderator
  • I am new to sql scripting and its my first time to use triggers. I have two tables e.g Table1 and Table2. I  made an INSERT  trigger in both tables when I add values in Table1, the values that I added in Table1 will also be added in Table2. When I add values in Table2 those values also will be added in Table1.

     

    This is my sample code in Table2.

     

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER TRIGGER [dbo].[InsertTable2] ON [dbo].[Table2] AFTER INSERT

    AS

    BEGIN

    BEGIN TRAN

    INSERT INTO Table1( m_valcode, m_valname,m_valdate)

    SELECT p_valcode, p_valname, p_valdate FROM inserted

    COMMIT TRAN

    end

     

    This is my sample code in Table1.

     

     set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER TRIGGER [dbo].[InsertTable1] ON [dbo].[Table1] AFTER INSERT

    AS

    BEGIN

    BEGIN TRAN

    INSERT INTO Table2( p_valcode, p_valname,p_valdate)

    SELECT m_valcode, m_valname, m_valdate FROM inserted

    COMMIT TRAN

    end

     

    After I execute both trigger, I add some values in the tables.There is an error in adding a record and the error is "Violation of Primary Key constraint 'PK_SetupSource'. Cannot insert duplicate key in object Table2(if I add values in Table2). I already check if the inputed primary key has duplicates. The inputed primary key has no duplicates. I really don't know how to solve this kind of problem. Can you help me on this?

     

     

     

    Thursday, October 04, 2007 1:45 PM
  • this is just like a endless loop.... WHen u insert into table1 its trigger fires and insert into Table2 ... then the trigger of Table2 fires which insert into Table1 ... and it will go on and you will get a error like this

     

    Msg 217, Level 16, State 1, Procedure tt2instrg, Line 5

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

     

     

    so change the logic...

     

    Madhu

     

    Thursday, October 04, 2007 2:09 PM
    Moderator
  •  

     I conducted a research in http://support.microsoft.com/kb/215059

     

    The following errors may appear in the Statmgr.log:
     This is same error as mine.....
    SQL Err #10007> General SQL Server error: Check messages from the SQL Server.
    SQL Msg #2627> Violation of PRIMARY KEY constraint 'StatusMessages_PK'. Cannot insert duplicate key in object 'StatusMessages'.
    SQL Msg #3621> The statement has been terminated.
    And it stated inorder to resolve this problem is to obtain the latest service pack for Systems Management Server 2.0 which is SMS 2.0 SP4.0.

     
    Thursday, October 04, 2007 3:13 PM
  • if i add values in table1 there will be an error "Violation of PRIMARY KEY constraint 'StatusMessages_PK'. Cannot insert duplicate key in object 'StatusMessages. The statement has been terminated."-

    If i delete the insert trigger in Table2. There will be no error if added values in Table1 and the values that i added in Table1 will be added also in Table2.

    Thursday, October 04, 2007 3:20 PM
  •  

    Thanks for the help.
    Thursday, October 04, 2007 4:25 PM