Microsoft KB Archive/263887

= BUG: Upgrade of an Empty Table Resets Identity Value to Seed =

Article ID: 263887

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q263887



BUG #: 55369 (SQLBUG_70)

BUG #: 214696 (SHILOH)



SYMPTOMS
If you insert rows into a Microsoft SQL Server 6.5 table that has an identity column, then you delete all the rows in the table, and subsequently upgrade the table to Microsoft SQL Server 7.0 or Microsoft SQL Server 2000, the identity value in the upgraded table is reset to the original seed value of the table in Microsoft SQL Server 6.5.



WORKAROUND
If you must guarantee that the identity values chosen in the upgraded database do not include those that were previously used in the now-empty SQL Server 6.5 table, use the DBCC CHECKIDENT statement to reset the next identity value for the table after the upgrade.

To determine what the current value for the identity column is in the SQL Server 6.5 database, use the DBCC CHECKIDENT statement with the NORESEED parameter. Then run the DBCC CHECKIDENT statement in the upgraded database with the RESEED parameter to set the identity value to the proper starting point.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.