none
Error 2601: Cannot insert duplicate key row in object

    Question

  • We have suddenly started running into errors when attempting to publish to a MDS entity, both from the Excel add-in and the Web application.

    When trying to add a member using the web app we encounter the error "2601: cannot insert duplicate key row in object 'mdm.tbl_2_30_EN' with unique index 'ux_tbl_2_30_EN_Version_ID_Code'. The duplicate key value is (2, 106229)."

    I have queried the table mdm.tbl_2_30_EN and it seems that ID 106229 would be the next key created.  It does not exist yet, so I do not know why this error is occurring.

    Has anybody ran into this problem using MDS before?  Why would this entity suddenly become corrupted somehow?

    Any ideas would be appreciated.

    Thanks.

    Tuesday, September 02, 2014 9:48 PM

All replies

  • Solution http://ollysense.blogspot.com/2011_11_01_archive.html


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 02, 2014 10:18 PM
  • I'm sorry Satish, but that solution does not make much sense to me.  I've read through that blog post a couple times and I do not see the solution. Where exactly is the fix?
    Tuesday, September 02, 2014 10:37 PM
  • Tony - The last line mentions that the WHERE clause of the unique constraint was inadvertently modified. It should be a filtered index (WHERE ([VersionMember_ID] IS NOT NULL))


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 02, 2014 11:00 PM
  • My error is occurring on a different index though.

    The unique index in my problem is "ux_tbl_2_30_EN_Versoin_ID_Code", while Olly's error was occurring in "ix_tbl_3_38_EN_Version_ID_VersionMember_ID".  

    I'm sorry, but I don't understand how to relate that solution to my problem.  

    Tuesday, September 02, 2014 11:13 PM
  • You might be having a similar problem, where the index you mentioned was supposed to be a filtered index but inadvertently got modified to be a regular index, which would indicate the duplicate error problem. You may need to figure out if this is the case by looking at the schema history report for the database (right click DB -->reports-->schema history...)

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 03, 2014 3:17 AM