Tough problem? Rolling back timed out Stored Procedure called from ASP?

[M Wells]

Hi All,

I don't know if this is a truly esoteric question, or not, but I'm
wondering how (or even if) you handle a timeout on a transaction
within a stored procedure executed in a Stored Procedure?

A theoretical example of the stored procedure (sadly, I'm not allowed
to post the actual code):


Create proc sp_testproc @myval int, @outval int OUTPUT as

begin tran

select top 1 @outval= myname from mytable where recid = @myval

update mytable set myname='xxxx' where recid = @myval

commit

select @outval


So, again in theory, I'm executing this stored procedure from an ASP
page.

After executing the stored procedure, I'm testing the ADO connection's
error object for a timeout.

What I'd like to know, is that if the stored procedure times out (ie
ADO.Error.Number = -2147217871) and I issue an ADO.RollbackTrans in
response to that error, will that rollback the transaction contained
within the Stored Procedure?

Many, many, many thanks in advance for any help with this!!

Much warmth,

Murray