Mysterious Loss Of Data From Mssql2k

Hi All,

I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'

To explain our situation:

We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.

When that page is submitted, the form data is passed to a stored
procedure on our mssql2k server, which performs several operations,
all of which are wrapped in a transaction.

In particular, the stored procedure performs an update operation on a
record in one table (i'll call it TableA) and an insert into another
table (TableB).

If the procedure encounters a problem (ie after each update / insert
operation in the procedure we test for IF @@Error<>0) it performs a
rollback, performs a select similar to the one immediately below, and
then RETURNs.

SELECT '1' as error, 'Unable to update TableA' as errormsg

If the procedure doesn't fail any of the @@Error tests, the
transaction is committed, and a membership number is SELECTed to be
returned.

SELECT '0' as error, @memnum as membershipnumber

The @memnum variable is populated within the transaction.

Back in the ASP page we test both for the proc returning an empty
recordset, or for it passing an explicit value in the error field, and
push the page to an error page if either of these conditions are met.

If, on the other hand, none of these conditions are met, and the
membershipnumber field in the recordset is populated with a valid
membership number, we push to a confirmation page.

This confirmation page receives the membership number in a session
variable, performs a SELECT against TableB (the table that received
the insert during the proc) using that membership number in the WHERE
clause, and the resultant recordset is used to populate the
confirmation details on that page. That recordset is also then used to
populate the details of a confirmation email, which is automatically
sent by the confirmation page.

And now here's our problem: we've become aware of a handfull of people
who have gone through the enrollment process, have received the
confirmation email containing the information they supplied as
expected, but the data appears to be entirely missing from our tables.
By that I mean that the record in TableA does not appear to have been
updated (under normal circumstances that record should have had
several flags set, and several other fields updated with information
supplied by the person enrolling), and the record in TableB does not
appear to have been inserted.

In essence, looking at our tables, it *feels* like the transaction in
the stored procedure for that particular enrollment hit a problem and
was rolled back. However, the evidence that we have in the form of the
confirmation email argues strongly that the data must have existed in
our tables (particularly in TableB), if only for an unknown period of
time.

We're kind of at our wit's end to work out what is going wrong with
these enrollments. From my understanding of transactions (and I could
well be wrong) any changes to data (ie updates, inserts etc) contained
within are essentially 'invisible' to any other operation (ie the
SELECT that happens in the confirmation page) until the transaction is
committed, implying that the effect of the update and insert should
have been 'permanently' successful if no error code is received and if
a valid membership number was returned. I ask, because someone in our
team has suggested that maybe the operations in the transaction
'lasted long enough' in the tables to have been visible for the SELECT
on the confirmation page to have worked, but were then subsequently
rolled back, explaining why the confirmation email is appropriately
populated and why the data then appears to be missing. However, as I
said, this doesn't match my understanding of how transactions behave.

Sorry for the length of this post, but I felt it was best to explain
this as best as I could.

Does anyone have any advice they can give us on this situation? ie,
are there any known problems with operations in transactions 'bleeding
over' into tables, but then being rolled back at some later point?
Does anyone have any thoughts or suggestions on how we can further
diagnose this issue?

Truly, any help will be immensely appreciated...

Thanks in advance,

M Wells