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)
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)
in this case all is well......
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
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.
> It is not faulty programming. Too many people have had the same
> My particular experience was with a simple set of triggers that inserted
> record into a log table whenever another table had an insert, update, or
> delete done. The log table had an IDENTITY column. Every few
> few thousand records the identity value would get screwed up--no other
> pattern. I stopped using IDENTITY.
> >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
> >Thank you.