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

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 RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
--
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> 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
>|||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
>
>.
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> > 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
>
>.
>