ERROR: ODBC/SQLServer Primary key violations 
Author Message
 ERROR: ODBC/SQLServer Primary key violations

We are experiencing this error message quite a lot.
Violation of promary key constraint 'PK_registratie_3__11'; attempt to
insert duplicate key in object 'registratie'.

registratie is a table in SQLServer 6.5 and has a nonclusterd primary
key on the field ID. We INSERT/UPDATE this table through an ASP page
with an ODBC connection to SQLServer.

When I check the contents of this table I don't see any duplicate
keys. When I make a copy of the table and insert the data from the
original; table, rename the new one to the old the problem goes away
for awhile.

Does anyone know what could be the culprit here? Is there a more
permanent solution than copying the tables once in a while.

Any help would be appreciated.
Nico Veenkamp.



Fri, 30 Mar 2001 03:00:00 GMT
 ERROR: ODBC/SQLServer Primary key violations

Of course you don't see any duplicate values, that is the reason why
you having a primary key.
Obviousely someone tries to INSERT a duplicate value but the databases
does not allow  this and creates a error message.

The copy makes no sense, the problem comes back when someone wants to
insert again a duplicate value.
Can't you trace the SQL that is causing the message (event log)

Luc



Quote:
>We are experiencing this error message quite a lot.
>Violation of promary key constraint 'PK_registratie_3__11'; attempt to
>insert duplicate key in object 'registratie'.

>registratie is a table in SQLServer 6.5 and has a nonclusterd primary
>key on the field ID. We INSERT/UPDATE this table through an ASP page
>with an ODBC connection to SQLServer.

>When I check the contents of this table I don't see any duplicate
>keys. When I make a copy of the table and insert the data from the
>original; table, rename the new one to the old the problem goes away
>for awhile.

>Does anyone know what could be the culprit here? Is there a more
>permanent solution than copying the tables once in a while.

>Any help would be appreciated.
>Nico Veenkamp.



Fri, 30 Mar 2001 03:00:00 GMT
 ERROR: ODBC/SQLServer Primary key violations
Nico,

Is ID an IDENTITY column? If it is, this is a known problem and you can use
DBCC CHECKIDENT(<table name>) to fix it. See the BOL, DBCC command.

HTH,
Ron



Fri, 30 Mar 2001 03:00:00 GMT
 ERROR: ODBC/SQLServer Primary key violations


Quote:
>Is ID an IDENTITY column? If it is, this is a known problem and you can use
>DBCC CHECKIDENT(<table name>) to fix it. See the BOL, DBCC command.

Yes it is. Is there a KB article on this bug/problem? Has it been
fixed in a SP? I'll try your solution and see if that really was the
cause of the problem.
Thanks for helping me out.

Nico Veenkamp



Sat, 31 Mar 2001 03:00:00 GMT
 ERROR: ODBC/SQLServer Primary key violations

Quote:

>Of course you don't see any duplicate values, that is the reason why
>you having a primary key.

Indeed.

Quote:
>Obviousely someone tries to INSERT a duplicate value but the databases
>does not allow  this and creates a error message.

I understand why it's giving me this error message. The cause however
is unclear to me.
I have an Active Service Page where someone can register to take part
in a discussion. This page checks to see if the loginname hasn't been
used by someone else and then uses the ODBC to INSERT the new data
into the SQL table. While inserting the new row the ID column should
be automatically add 1 to it's number, no? It's almost as if ODBC
tells SQL to expect a new row, SQL adds the new row with the new ID
number and then for some reason ODBC tries to insert the row with that
new ID number thereby causing the error message.

Nico Veenkamp



Sat, 31 Mar 2001 03:00:00 GMT
 ERROR: ODBC/SQLServer Primary key violations
Another option is to find the last inserted identity value and then do an
identity insert and set the value to something higher than the last value.
For example--Say your identity value is 680305 and the field names are ID,
Mystuff, MoreStuff, and your table is named ThisStuff  You could do the
following:

set identity_insert ThisStuff on
go
insert into ThisStuff (ID, Mystuff, Morestuff)
Select 680310, "SomeStuff", "SomeMoreStuff"
go
set identity_insert ThisStuff off
go

hope that helps!

Robert

Quote:

>Nico,

>Is ID an IDENTITY column? If it is, this is a known problem and you can use
>DBCC CHECKIDENT(<table name>) to fix it. See the BOL, DBCC command.

>HTH,
>Ron



Mon, 02 Apr 2001 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Getting this error: Primary Key violation when connection using ODBC

2. ODBC Violation of Primary Key Constraint Can't Update Reord

3. 23000: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint

4. Trapping Insert Error (i.e. Primary Key Violations)

5. Error: Violation PRIMARY KEY

6. Primary Key Violation Error

7. Primary Key Violation error

8. Violation of PRIMARY KEY error

9. Violation of PRIMARY KEY contraint error!!

10. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

11. Distribution Agent: Violation of PRIMARY KEY constraint error


 
Powered by phpBB® Forum Software