Very Computer

I have set up my tables to have auto-generated unique IDs for each record.
The ID field is set to "identity" and has a unique constraint on it making
it a key field.

I create new records from an ACCESS 2000 front end. It was working fine
until yesterday when an operator shown me an error message to the effect ;
"tblxxx , attempt to insert duplicate key (or identity) values)..... error
xxxx".

The record would not be inserted. I went into SQL server (6.5) enterprise
manager and looked at the table indexes. I clicked "rebuild" and under
distribution "update".  After this new records would be inserted.

I  do not know why the problem occurs in the first place. I also do not know
why I was able to clear the problem by manually rebuilding the indexes. Do I
need to have a procedure to automatically check the indexes?

Any help would be appreciated. Thanks.

I have found out since this post that someone reset the machine on which the
sqlserver is located.  I have also found out that the problem can be
corrected by using,
DBCC CheckIdent (tablename, tablename2...)

after the server is restarted.

Quote:> I have set up my tables to have auto-generated unique IDs for each record.
> The ID field is set to "identity" and has a unique constraint on it making
> it a key field.

> I create new records from an ACCESS 2000 front end. It was working fine
> until yesterday when an operator shown me an error message to the effect ;
> "tblxxx , attempt to insert duplicate key (or identity) values)..... error
> xxxx".

> The record would not be inserted. I went into SQL server (6.5) enterprise
> manager and looked at the table indexes. I clicked "rebuild" and under
> distribution "update".  After this new records would be inserted.

> I  do not know why the problem occurs in the first place. I also do not
know
> why I was able to clear the problem by manually rebuilding the indexes. Do
I
> need to have a procedure to automatically check the indexes?

> Any help would be appreciated. Thanks.

Cyprian,

     On SQL 6.5, Unfortunately, there is a known issue with the Identity
Value not being kept up to date accurately when the server is shut down
without issuing a checkpoint... .During teh recovery process that runs when
teh server is restarted, Identity Values can be lost, causing teh next one
to be issued to be a duplicate.  This supposedly can only happen after a
Shutdown without a checkpoint, so I would c=investigate that possibility
first.  If so, running DBCC CheckIdent will correct the problem... If this
can and will happen regularly, you can protect yourself by implementing an
AutoStart Stored Proc that runs DBCC CheckIdent on every table during server
startup routines.....

Regards,

Charly

Quote:> I have set up my tables to have auto-generated unique IDs for each record.
> The ID field is set to "identity" and has a unique constraint on it making
> it a key field.

> I create new records from an ACCESS 2000 front end. It was working fine
> until yesterday when an operator shown me an error message to the effect ;
> "tblxxx , attempt to insert duplicate key (or identity) values)..... error
> xxxx".

> The record would not be inserted. I went into SQL server (6.5) enterprise
> manager and looked at the table indexes. I clicked "rebuild" and under
> distribution "update".  After this new records would be inserted.

> I  do not know why the problem occurs in the first place. I also do not
know
> why I was able to clear the problem by manually rebuilding the indexes. Do
I
> need to have a procedure to automatically check the indexes?

> Any help would be appreciated. Thanks.