Deadlocks, severity level, and HRESULT

I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
COM+, and VB6SP5. We have been having some problems with transactions
not being rolled back when an error was raised, and we have traced it
to the following situation. I'm looking for strategies of error
handling for this situation.
Sometimes (most times?) when there is a deadlock, Err.Number (HRESULT)
is 80004005 (-2147467259), but SOMETIMES IT IS 0 (that is, no error).
So because my code was not told about the error, it kept on
processing, but the problem is, THE DTC TRANSACTION HAS BEEN ABORTED,
so all further updates are not in a transaction. Usually DTC wakes up
sooner or later, but by then it might be too late - I may have made
updates thinking I was in a transaction when I was not. These updates
cannot be rolled back, and my database is left in an inconsistent
state. (FYI, the error I get from DTC are 8509 in the profiler,
reflected as 3704 in VB, if it is detected during processing. If it
is not detected until the root object is exiting, I see 8004e002.)
In particular, so far we have seen only ONE section of code where it
sometimes returns 0. All other areas of the code so far have returned
80004005. Also, in the place that returns 0, it only SOMETIMES
returns 0. Other times, it returns 80004005. There does not seem to
be any rhyme or reason to when it returns 0 and when it returns
80004005 (not that I've discovered yet, though). In all cases, the
error information in the profiler trace either:
Error: 1205, Severity: 13, State: 8
or
Error: 1205, Severity: 13, State: 50
There doesn't seem to be any correlation between the state=8 or the
state=50 errors.
All the error handling samples in MSDN use the "on error goto" trick,
but obviously that doesn't work if Err.Number = 0. Is it safe to just
check cn.Errors? Would there ever be any "informational" or "warning"
errors in there that I really should just leave alone and not report
an error? Is Deadlock the only one that could return 0 that really
should be aborted?
Thanks for any strategies you might know about!
Christine Wolak -- Senior Programmer
www.axiom-corp.comkaligrrl@.yahoo.com (Christine) wrote in message news:<d6363ccb.0309120837.1fc9d3e2@.posting.google.com>...
> I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
> COM+, and VB6SP5. We have been having some problems with transactions
> not being rolled back when an error was raised
Okay, we have the answer. SET NOCOUNT ON in your stored procedure.
Always. In any stored procedure that you will be calling from a
client. Here is a link to a KB article:
http://support.microsoft.com/?kbid=240882 . How it can be "by design"
that an error gets raised and is not reported back to the caller, I
don't know, but apparently Microsoft does not plan to address this
issue any time soon. This article also says "a SQL Server trace may
reveal excessive attentions and rollbacks". We saw NONE of that.
NOTHING excessive - only EXPECTED stuff.
In our particular case, we had a deadlock earlier in the process,
during a stored procedure call. But since we were not told about it
(via Err.Number), our code kept processing. We did not have really
"unexpected" problems, because the stored procedure was a "fastpath",
and if for any reason the fastpath cannot be taken, then the code
takes the long path. And the stored procedure return code of 0 just
happens to indicate that the long path was necessary. So the code
just merrily went along, continuing to process.
Which for some reason DTC/COM+/MTS/whatever, let it keep doing. But
it's no longer in a transaction, and any updates at this point are
COMMITTED. At some point later, DTC would wake up and say "hey,
you're not supposed to still be doing stuff!" At this point, we might
see 3704, or 8004d00a (-2147168246), or, if it got all the way to the
very end and is exiting the root object, 8004e002 (-2147164158).
We had several stored procedures that work similarly, and only one had
the problem, and even then not every single time. The difference
would be the amount of informational messages passed back to the
client (DONE_IN_PROC messages).
So just take it from me - always use SET NOCOUNT ON in all stored
procedures called from VB/C++ except for the specific parts of data
that you want returned to the caller, and realize that, if you don't,
you could at any point stop receiving errors from SQL Server.
Christine Wolak -- www.axiom-corp.com