| Author |
Topic |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2009-02-20 : 17:40:57
|
Hi all,I have a dataset with several records. When I update the MS SQL Table using a stored procedure I get an error message like 'Primary Key Exception. Could not insert duplicate record into table'. I've checked the logs and the records to be uploaded but could not find any duplicate key. Unfortunately SQL Server does not return any details, e.g. some details about the duplicate records would be fine...  Is there anything I could do to get more details about this error?Many thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-20 : 17:42:43
|
| Is primary key Identity column as well? |
 |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2009-02-20 : 17:45:29
|
| No, primary key is created from 2 Integer-Columns (both 'not null' of course) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-20 : 18:20:11
|
quote:
Originally posted by Heinz23
No, primary key is created from 2 Integer-Columns (both 'not null' of course)
I mean Pk key has identity property defined. |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2009-02-20 : 18:29:20
|
quote:
Originally posted by sodeepI mean Pk key has identity property defined.
Sorry, but I don't know what you mean.... In SQL Management Studio I just clicked on these 2 columns and on Icon 'Set primary key'. I did not change anything with identity property. But I've checked now 'Identity Specification', and there '(Is Identity)' is set to 'No' both my both PK-Columns. Is this what you mean? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-21 : 01:55:31
|
quote:
Originally posted by Heinz23
Hi all,I have a dataset with several records. When I update the MS SQL Table using a stored procedure I get an error message like 'Primary Key Exception. Could not insert duplicate record into table'. I've checked the logs and the records to be uploaded but could not find any duplicate key. Unfortunately SQL Server does not return any details, e.g. some details about the duplicate records would be fine... Is there anything I could do to get more details about this error?Many thanks!
are you trying to update pk column as well? if yes you need to make sure the values you put for two integer columns does not already exists in tablesomething likeUPDATE tableSET IntCol1=Val1,IntCol2=Val2,...WHERE NOT EXISTS (SELECT 1 FROM table WHERE IntCol1=Val1 ANDIntCol2=Val2) where IntCol1 & IntCol2 are integer columns which form PK |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2009-02-21 : 03:23:07
|
| No, I'm not doing an update, only an Insert. I use Microsofts Enterprise Library. I've checked before the INSERT that the rows do not exist, the query returns 0 rows. Then I add 17 rows to a datatable. I've logged it into a textfile and there are no double primary keys, everthing seems to be correct. But then on the update I get this error. Nevertheless the data IS STORED in the SQL database....?? I don't make a second INSERT so it seems that it ran fine but nevertheless the error is thrown.... |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2009-02-21 : 03:28:46
|
OK, found it!!The problem was with the last row, it really was duplicated. My internal datatable had 17 rows while on SQL only 16 were stored. (I was just confused because my internal table is of course 0-based and so it says the last row was nr 16....  ). So it seems that the enterprise library (or the SQL 2005) adds all rows and stops then when the problem appears so I could identify and it's correct, the last row is really duplicated!!So thanks for all your help, but as expected it was a layer 8 problem  |
|
|
|
|
Avoid duplicate primary key error