Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.
please show us your exact stored procedure code.
thanks.
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1an$qp31@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>