Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Unanswered: Duplicate key was ignored warning returned even when no duplicates are found

    Hi
    I am having problems with the "Duplicate key was ignored" warning message. The problem is that the message seems to happen randomly and cannot be reproduced. If i take the same set of data and run the stored procedure that causes the problem i don't get the warning message a second or subsequent time. Also all the SELECT statements have criteria set to remove duplicates before they are inserted into the tables.

    Background to the problem:
    I have a data feed that pulls data from a DB2 database to a SQL Server 2008 staging table as a flattened set of records. A stored procedure in SQL Server is run to load the data into the destination tables. The data feed is run hourly for new and updated records in DB2 Monday-Friday 09:00-17:00 and then there is a midnight run of all the records going back for the last 12 months.

    The data feed was originally sent from DB2 as a CSV file and pulled into SQL Server using SSIS but is now an Informatica workflow that pulls the data directly from DB2.

    It is the Informatica workflow that is returning the "duplicate key was ignored" warning message and this stops the workflow. The workflow is restarted and the data is always loaded the second time without the warning message. The warning does not happen every time the workflow is run - it can run for a number of days with no warnings and then one will come through

    I can see in Profiler that it is SQL Server that returns the Duplicate key was ignored warning message so it is not an issue with Informatica.

    I cannot reproduce the problem to get to the root cause of the issue. I would expect that if i run the same set of data through the stored procedure i would get the warning message every time, but this is not the case. Even when i step through the stored procedure i do not get the message. As the midnight data feed returns the records from the last 12 months, so by definition would include duplicates, the warning message only appears randomly and is not consistent.

    Is this a bug in SQL Server or does anyone have a suggestion of where else to look?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    One gets a "Duplicate key was ignored" warning message when the UNIQUE INDEX was created with IGNORE_DUP_KEY = ON.

    CREATE UNIQUE INDEX XXXXX ON DaTable (id)
    WITH (IGNORE_DUP_KEY = ON);


    It will insert the records that did not violate the uniqueness constraint. Does Informatica workflow roll back the whole transaction? As it's only a warning.

    - Sounds like there are (sometimes) multiple processes inserting into or updating the same destination table(s). What other processes run around the same time? User input? All those hourly runs could sometimes collide with user input or another process.

    - Rewrite the INSERT statement, so it will only insert records that won't cause a duplicate key violation. As opposed to a sequence of two or more SQL commands that first remove or tag the duplicate records, and insert with another SQL command.
    Another SQL command (e.g. from user input) could sometimes run in between that sequence of SQL commands and enter a record that will later give rise to the Duplicate Key error.

    Donno if this will help you, just my 2 cents. Good luck.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Wim View Post
    Rewrite the INSERT statement, so it will only insert records that won't cause a duplicate key violation
    ^^ this.
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    ^^ this.
    What do you mean, Willis?

    I mean that the INSERT statement should be rewritten, so that the check for duplicates is part of it. One way to do that is:
    Code:
    INSERT INTO DaDestinationTable (id, col2, col3)
    SELECT DaSourceTable.id, DaSourceTable.col2, DaSourceTable.col3
    FROM DaSourceTable 
    	LEFT OUTER JOIN DaDestinationTable ON
    		DaSourceTable.id = DaDestinationTable.id
    WHERE DaDestinationTable.id IS NULL
    Instead of spreading it over multiple SQL commands, like:
    Code:
    UPDATE DaSourceTable
    SET InsertThisRecord = 0
    
    UPDATE U
    SET InsertThisRecord = 1
    FROM DaSourceTable as U
    	LEFT OUTER JOIN DaDestinationTable ON
    		U.id = DaDestinationTable.id
    WHERE DaDestinationTable.id IS NULL 
    
    --bad things could happen now
    
    INSERT INTO DaDestinationTable (id, col2, col3)
    SELECT id, col2, col3
    FROM DaSourceTable 
    WHERE InsertThisRecord = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    What do you mean, Willis?
    Willis was indicating that he agrees with you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    Willis was indicating that he agrees with you.
    For all I know, the emoticon "^^" stands for "laughing".
    But I feel far better by accepting your explanation, Pat. So as a consequence I like Willis even more.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nah, not hardly laughing...

    Quote Originally Posted by gvee View Post
    ^^ this.
    Is newspeak for "see this stuff above" and it implies hearty agreement.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hearty agreement indeed... Arnold!
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by gvee View Post
    Hearty agreement indeed... Arnold!
    Brothers for life !
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •