Ошибка sql state 23000 native 2627

Answers

• Looks like the table is designed in such a way that the field must have only unique values. The answer to your question depends on where is the duplicate coming from. Is the key an identity field? Is it being generated in the SP or it’s being passed as a parameter? We want to understand what part of your code is generating the unique values so we need more details.

  Thanks,

  ---

  Varsham Papikian, New England SQL Server User Group Executive Board, USA
  
  New England SQL Server User Group;
  My LinkedIn page
  
  Please remember to click the «Mark as Answer» button if a post helps you!

  • Marked as answer by

    Thursday, August 27, 2009 9:03 AM

• Hi Hetan,
  There are some Primary Key issues in this 2 tables…

  1) ‘dbo.CharApplySkills’
  2) ‘dbo.CharSavePoint’

  I think there may be 2 solutions :

  1st : Identifying how duplicate data is coming to both the tables. Ideally it should not come.
  2nd : If not identified… Try removing : the primary key constraint.

  1st solution is recommended.

  Please Vote & «Mark As Answer» if this post is helpful to you.

  Cheers
  Bikash Dash

  
  MCDBA/MCITP

  • Edited by
    Bikash Dash
    Thursday, August 27, 2009 5:38 AM
  • Marked as answer by
    Hetan
    Thursday, August 27, 2009 9:03 AM

• Please give us the table structure of 2 tables.

  1) ‘dbo.CharApplySkills’
  2) ‘dbo.CharSavePoint’

  Please Vote & «Mark As Answer» if this post is helpful to you.

  Cheers
  Bikash Dash

  MCDBA/MCITP

  • Marked as answer by
    Hetan
    Thursday, August 27, 2009 9:03 AM

• I checked the package and I see that it doesn’t have the code piece we need.

  Right click on the database, choose Tasks and then Generate Scripts. Follow the wizard. Just script the whole database (tables, indexes, SPs…).

  Then post the code.

  Thanks,

  ---

  Varsham Papikian, New England SQL Server User Group Executive Board, USA
  
  New England SQL Server User Group;
  My LinkedIn page
  
  Please remember to click the «Mark as Answer» button if a post helps you!

  • Marked as answer by
    Hetan
    Thursday, August 27, 2009 8:33 PM

• I did some troubleshooting. Here is the result:

  I see three errors: in all cases you have a primary key defined for a table (single column one or a multiple column one) and your Stored Procedures try to insert duplicate rows.
  The values for the keys are NOT generated inside your Stored Procedures so the SPs are not responsible for the error. 

  Resolution:  you have to check the code (app code?) which is specifying the input parameters for the SPs and check the logic there. I am assuming that the Primary Keys are defined ‘right’ and the column/s values in the PK have to be unique for the application to work properly. How is the application making sure that it doesn’t specify ‘bad’ input param values when calling the SPs?

  Your application could catch this specific type of error (dup values) and do something with it. Or you could do some extra checking in the SPs.
  You could do other improvements too: for example ISNULL in ISNULL(count(*), 0) is redundant since the result of count(*) is never NULL (even if there is no qualifying row).

  More details:

  1.
  SP used: usp_Create_GuildChar_E
  Updates the table: GuildChars
  Primary key [PK_GuildChars] on [GuildID],[CharID]
  Call: usp_Create_GuildChar_E 6, 211, 0
  Fail reason: a row where [GuildID]=6  and [CharID]=211 already exists so INSERT conflicts with Primary key [PK_GuildChars].

  2.
  SP used: usp_Save_Char_ApplySkill_Add_E
  Updates the table: CharApplySkills
  Primary key [PK_CharApplySkills] on [CharID],[SkillID]
  Call: Usp_Save_Char_ApplySkill_Add_E 478,30,3,300
  Fail reason: a row where [CharID]=478 and [SkillID]=30 already exists so INSERT conflicts with Primary key [PK_CharApplySkills].

  3.
  SP used: [usp_Create_Char_SavePoint]
  Updates the table: CharSavePoint
  Primary key [PK_CharSavePoint] on [CharID]
  Call: usp_Create_Char_SavePoint 1686,1,2,843.802917,39.217873,1771.947388
  Fail reason: a row where [CharID]=1686 already exists so INSERT conflicts with Primary key [PK_CharSavePoint].

  Hope this was useful for you and you can identify the core issue and improve the application.

  Thanks,

  ---

  Varsham Papikian, New England SQL Server User Group Executive Board, USA
  
  New England SQL Server User Group;
  My LinkedIn page
  
  Please remember to click the «Mark as Answer» button if a post helps you!

  • Marked as answer by
    Hetan
    Friday, August 28, 2009 10:13 AM

• Did the application work before as a whole or you are just starting to develop it? If we assume that the back-end design is ‘right’ then you need to check the module which calls the SPs and make sure it doesn’t pass values which conflict with the primary keys. 

  As you see, the answers are starting to depend on many internal details in your application. Not that I don’t want to continue, but you need to formalize the questions a little bit better so that we have enough info for answering without going into the application details

  Thanks,

  ---

  Varsham Papikian, New England SQL Server User Group Executive Board, USA
  
  New England SQL Server User Group;
  My LinkedIn page
  
  Please remember to click the «Mark as Answer» button if a post helps you!

  • Marked as answer by
    Hetan
    Friday, August 28, 2009 9:33 PM

Not your computer? Use Guest mode to sign in privately. Learn more

I need to catch violation of UNIQUE constraints in a special way by a C# application I am developing. Is it safe to assume that Error 2627 will always correspond to a violation of this kind, so that I can use

if (ThisSqlException.Number == 2627)
{
    // Handle unique constraint violation.
}
else
{
    // Handle the remaing errors.
}

?

SELECT * FROM sys.messages
WHERE text like '%duplicate%' and text like '%key%' and language_id = 1033

4

    public static bool IsUniqueKeyViolation(this SqlException ex)
    {
        return ex.Errors.Cast<SqlError>().Any(e => e.Class == 14 && (e.Number == 2601 || e.Number == 2627 ));
    }

1

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.

3