salamol (salamol@hotmail.com) writes:
> The problem I am facing is neither cannot get an identity (identity not
> null) nor getting a wrong identity. the server returns a correct
> identity value which should belongs to the record that I just insert,
> but when I fetch the record by that identity value, the record is
> missing i.e. the identity for the last record is 100, and I do an
> insert, the system return 101 as @@identity and 0 as @@Error, however if
> i select the record from 95 - 105 it will returns records 95, 96, 97,
> 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> insert after 101)

If you have received 101 in @@identity for an inserted row, and the row
is later nowhere to be found, the insertion of the row has been rolled
back, as I discussed in my other post.

While you may already be aware of this, I like to point out that it lies
in the concept of the IDENTITY property that you never can trust the numbers
to be contiguous. If you attempt to insert a row into a table, and that
insertion fails for whatever reason, you have still consumed a number.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp