Goofed up Identity problem

[Joshua Langemann]

We have a problem in SQL Server 6.5, where the internally stored identity
values get out of sync with their associated tables when the server
crashes. This results in the violation of primary key constraints on the
affected tables when subsequent inserts are done, as SQL Server attempts to
insert a value that already exists.

The obvious solution would be to use DBCC CHECKIDENT, which sets the
identity value based on the maximum value in the table. However, we audit
changes to the tables in a separate database, and if the highest identity
value for a table was already used and the record deleted, we can't re-use
the value, since it is stored in the audit database. Thus, setting the
identity value based on the highest value in the base table isn't
sufficient; we need to take the audit database into account as well.

Does anyone know of a way of setting the internal identity value for a
table, perhaps using some undocumented statement? We would be much
appreciative of any useful tips you can give us.

Thanks in advance,

--
Joshua Langemann
Computronix Holdings Limited
e-mail: jos...@computronix.com

[Bob Pfeiff]

Since you need closer control over this value, your best bet is probably to
do your own "identity" column (e.g., max(id) + 1).

--
Bob Pfeiff
MCSD, MVP
Spectrum Technology Group, Inc.
bo...@spectrumtech.com

[William Zhou]

SET IDENTITY_INSERT tablename ON

Will let you insert value into identity column.

William Zhou

[Russell Jones]

You do a UNION select statement on both the primary and the second
(separate) database and select the maximum value + 1 to use as the new
starting identity value.

Joshua Langemann <jos...@computronix.com> wrote in article
<01bdc791$e35ab580$9964...@chl0063r.edmonton.computronix.com>...