Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 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