Identity columns and invalid identity values.

Identity columns and invalid identity values.

Post by Jeff Christense » Fri, 19 Sep 1997 04:00:00



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.

 
 
 

Identity columns and invalid identity values.

Post by Marc Padovan » Fri, 19 Sep 1997 04:00:00


 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.


>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.


 
 
 

Identity columns and invalid identity values.

Post by Tibor Karasz » Fri, 19 Sep 1997 04:00:00


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

 
 
 

Identity columns and invalid identity values.

Post by Phil Turtl » Sat, 20 Sep 1997 04:00:00


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

 
 
 

Identity columns and invalid identity values.

Post by Roy Harv » Sat, 20 Sep 1997 04:00:00


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?

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).

Roy

 
 
 

Identity columns and invalid identity values.

Post by Jeff Christense » Sat, 20 Sep 1997 04:00:00



> Jeff,

> >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?

> 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).

This coincides with our observations. It definitely seems to happen
after a restart. Unfortunately it is not consistent.

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

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

 
 
 

Identity columns and invalid identity values.

Post by Greg Stande » Sat, 20 Sep 1997 04:00:00


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


> appreciated.

 
 
 

Identity columns and invalid identity values.

Post by Alan Zenreic » Wed, 24 Sep 1997 04:00:00


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.



>  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.


> >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.

 
 
 

Identity columns and invalid identity values.

Post by trevor bake » Fri, 26 Sep 1997 04:00:00


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).

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

tia
trevor baker

 
 
 

Identity columns and invalid identity values.

Post by Roy Harv » Tue, 30 Sep 1997 04:00:00


Trevor,

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

It resets the next identity value to the correct number.

Roy

 
 
 

1. Setting the Next identity value on an IDENTITY column

Hi,

I have several tables that are using the IDENTITY property.  I would
like to execute a query that will set the next id value to be used.
In know this can be done by inserting a record at a specific value but
I would like to do this without inserting a record.  The tables are
already built and it is not possible to recreate them (at least not
very easily).

I tried looking for the system table that stored the next Identity
value but I was not able to find it.

Thanks in advance.

later,
Mark

2. DE - Senior Level Oracle Developer Needed

3. how to alter an identity column to a non-identity column

4. MSSQL Tool !!

5. How do make an identity column NOT an identity column

6. Licensing

7. How to change an identity column to non identity column

8. Generating identity values without IDENTITY keyword

9. Retrieve Identity value (select @@identity...)

10. Attempt to insert duplicate identity values in table (has identity and key fields)

11. Identity column reverts to non-identity

12. changing an identity column to non identity