Cannot insert duplicate key into ...... EXTREME problem

Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill

>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill

>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>