Very Computer

While the documentation is clear that the current identity value
for an identity column can become invalid it does not indicate
what will cause this situation. I know how to fix the value
with the DBCC CHECKIDENT(<table>) command. I suspect some faulty
programing in my ODBC application but am looking for clues as
to where to look for the real problem. Does anyone have suggestions
as to what typically will cause an identity value to become
invalid?

Thank you.

appreciated.

 It is not faulty programming.  Too many people have had the same problem.
My particular experience was with a simple set of triggers that inserted a
record into a log table whenever another table had an insert, update, or
delete done.  The log table had an IDENTITY column.  Every few weeks/every
few thousand records the identity value would get screwed up--no other
pattern.  I stopped using IDENTITY.

I believe that the handling was "a bit" stabilized in a service pack, but I
really have no details...
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB

It is faulty programming - it's just that the fault lies with Microsoft.
This sort of functionality has been around for many years in other RDBMS so
what's the big problem with doing it correctly in MS SQL Server?

                Phil Turtle
                Wang

Jeff,

Quote:>While the documentation is clear that the current identity value
>for an identity column can become invalid it does not indicate
>what will cause this situation. I know how to fix the value
>with the DBCC CHECKIDENT(<table>) command. I suspect some faulty
>programing in my ODBC application but am looking for clues as
>to where to look for the real problem. Does anyone have suggestions
>as to what typically will cause an identity value to become
>invalid?

Roy

Thanks to every one who replied. It helped a lot.

I will leave the anti-Microsoft rant as an exercise for the reader.

I found this to occur randomly (maybe an average every 20th rollback) after
I did a
rollback on an insert.  I had a case where I would insert a row, get the
IDENTITY,
start to insert on another table and find a problem.  Then, I would
rollback the
first table.  About every month or so, this causes this problem (which for
this table,
this situation occurs maybe 1 in 20 times).  Certainly not predicatable
however.

Greg

We came across a similar situation, and our observations may or may not
apply to you... here's what was happening to us.

We have a table with invoice IDs which are an ident field
The primary key is the invoice# (an Ident field)

Invoice table
----------
2    
3    
5      

and we have a log table where we move and store voided invoices....
the primary key is the invoice# shown below (not an ident field)

InvVoid table
-----------
1
4

in this case all is well......

HOWEVER....
if the user decides to delete invoice #5 from the Invoice table and insert
it into the voided table.... this is fine... PROVIDED it does not happen at
the very end of the day..... because our nightly maintenance runs CHECK
IDENT  which has the * sideeffect of setting the NEXT available
Invoice# to 5.

If any user creats a new invoice the next morning, it gets the #5, and
consequently can't be voided because there already was another #5 in the
voided table.  * stuff when you can come up with duplicate ident
values.

Btw, we are running checkident every night because of problems that were
arising where we were getting keyviolations in the Invoice table, a
situation that seems to happen from time to time with ident fields as keys.

Hope this helps.

Quote:> > The bug(s) that I have heard about were all related to stopping and
> > starting SQL Server.  The workaround was to write a stored procedure
> > that runs DBCC CHECKIDENT for every table using identity, and then use
> > sp_makestartup to designate that procedure to run every time SQL
> > Server starts.  There is also real hope that SP3 for release 6.5 of
> > SQL Server finally fixed the bug(s).

tia
trevor baker

Trevor,

Quote:>does DBCC CHECKIDENT(<table>)  repair any inconsistancies it find? or
>simply report the discrepancy?

Roy