-2

While inserting a record to my table table1 in SQL Server 2008 instance, I'm getting the following error

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'IX_table1'. Cannot insert duplicate key in object 'dbo.table1'.

But while executing the same query in SQL Server 2014 (copy of the above database), I am getting some difference in the error messsage

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'IX_table1'. Cannot insert duplicate key in object 'dbo.table1'.
The duplicate key value is (xxx, ).

In 2014 the error message specifies the duplicated value in the message (The duplicate key value is xxx), but in 2008 it does not.

Why this? Is this a new feature on 2014? or there is any settings to change/format the error messages?

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • Please post your table structure and key constraints for both environments. – Raj Apr 04 '16 at 09:08
  • What are the definitions of `PK_AsrAssetMst` and `IX_AsrAssetMst`? – strickt01 Apr 04 '16 at 09:13
  • They just configured the error messages differently, as you can see in 2014 it even specified the value that raised the trigger, but its the exact same error. – sagi Apr 04 '16 at 09:13
  • sorry, the question is modified. plz chk now. In 2014 is specified the duplicated valu in the message but 2008 not – Abdul Rasheed Apr 04 '16 at 09:14
  • As @sagi said then, it's the same error with extra information on what the duplicate key value is. – strickt01 Apr 04 '16 at 09:14
  • 5
    Why do you care what the error message says? Surely you're not using string comparison to detect this particular error? Since you're not particularly clear *what* you want done here I'm going to assume you either want SQL Server 2008 to also report which value was attempted inserted, or you want SQL Server 2014 to *not* report it. Either way you cannot change this but it should be unnecessary. The error message should not be shown to the user, in which case it does not matter what it says, you also know the value since you just tried to insert it, and the error code is 2627, in both cases. – Lasse V. Karlsen Apr 04 '16 at 09:15
  • But we should know, why diff instances behave like this. We should care about our server (this may be version diff, configuration diff or may some fault from my side). May it is not a silly – Abdul Rasheed Apr 04 '16 at 09:23
  • 2
    They changed the error message in 2014 (or an interim version) to contain more information. Your question is how to reconfigure or format the error messages. If you want to know *why* the difference exists you must likely ask the SQL Server team. – Lasse V. Karlsen Apr 04 '16 at 09:24
  • Actually my question is not to configure, but Why this diff? (is there any do reference for *They changed the error message in 2014* @Lasse V. Karlsen – Abdul Rasheed Apr 04 '16 at 09:25
  • Down voters, plz check the answer of Remus Rusanu :) We got a new lesson here. and this feature is Very helpful , while inserting Set of data / bulk insert as mentioned by @Remus Rusanu, – Abdul Rasheed Apr 04 '16 at 09:41
  • If the question asks "can I change this" and the answer says "this was changed because" then the down-votes are correct. If the correct answer to the question *does not answer the question* then the question is incorrect. – Lasse V. Karlsen Apr 04 '16 at 09:45
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/108159/discussion-between-abdul-rasheed-and-lasse-v-karlsen). – Abdul Rasheed Apr 04 '16 at 09:48
  • can any one please add a reference / link for : *In SQL Server 2014 there was an improvement to capture the duplicate key*, Thanks in adv – Abdul Rasheed Apr 04 '16 at 12:19

1 Answers1

6

There was a big demand from users that when the PK violation occurs, to display the value that crated the conflict. When the violation occurs in a simple INSERT statement that inserts one row then is a no brainer, the violation occurred due to the value being inserted. But INSERT can insert also a set, eg. INSERT INTO ... SELECT ... FROM... and then is much harder to figure out which row from the set caused the violation.

In SQL Server 2014 there was an improvement to capture the duplicate key that caused the violation and display it in the error message.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Hi, can u plz add a reference / link for *In SQL Server 2014 there was an improvement to capture the duplicate key*. Thanx in adv – Abdul Rasheed Apr 04 '16 at 10:58