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.If the transaction has NOT YET committed, but you are in code AFTER the
insert, you will get good results from @.@.error @.@.identity. But then later in
the same transaction, you are aborted the row will be removed...A deadlock
error would look like this, a batch abort would look like this also...
Use Profiler to see if the transaction is being aborted... Also look for
Erland Sommerskog on Google.. His web site has the best information availabe
about aborts...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1au$bh4@.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.
>