wierd SQL SERVER ERROR regarding index's etc

Posted on 1999-07-06
Medium Priority
Last Modified: 2012-05-04
Doh; I get this when ever I try and insert a new record in sql server via ASP;

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'UPKCL_empNewsID': Attempt to insert duplicate key in object

The wierd thing is that the primary key is an auto number stylie.'UPKCL_empNewsID' is a clustered index.

running UPDATE STATISTICS seems to temporaraly fix the problem but then it comes back. Any ideas?

Question by:matthewallum

Expert Comment

ID: 1096182
The identity counter may be messed up... run dbcc checkident on the table.  Does that fix it?

Accepted Solution

mitek earned 800 total points
ID: 1096183
we used to have that kind of problem with SQL 6.5 ... pretty sure it's still somewhere there in 7.0, although it's not that bad as in 6.5. that's a known bug with SQL Server ... "dbcc checkident tabname" will surely fix it, but the problem will still be there and will reappear.

one way to solve it would be to allow dublicate keys by replacing primary key with clustered non-unique index.

if this is not acceptable, then it's better to forego the use of IDENTITY feature altogether.

what we used to do was having a stored procedure (something like "nextval") that would make a new integer sequence value.

then, incerting a new record lookied like:

DECLARE @ident int
EXEC @ident = nextval 'valueid'
INSERT INTO temptab VALUES(@ident, ...)

to keep sequence numbers, we'd have a table called something like "sequences":

CREATE TABLE sequences (seqname varchar(30),currentval int,startval int,maxval int,increment int)
the nextval procedure would contain something like this:

CREATE PROCEDURE nextval @seqname varchar(30)
DECLARE @nextid int
SELECT @nextid = currentval FROM sequences WHERE seqname = @seqname
UPDATE @currentval = currentval + increment WHERE seqname = @seqname
RETURN @nextid

we found that this method is extremely reliable (especially if you do it in a transaction).

another improvement of this approach would be to put it in a trigger on the table and actually try to emulate autonumber behavior.

in fact, almost anything will be more reliable than IDENTITY + PRIMARY KEY combination ...


Author Comment

ID: 1096184
many thanks

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question