We have a suppliers application that runs a stored procedure (one of
many). This stored procedure then calls various other ones etc... and
the final number of stored procedures run is 11.

Now, some of our users have been having problems and deciding for
themselves to CTRL+ALT+DEL the application. This is the first thing we
will stop.

However, we have had some error messages indicating that the previous
transaction is blocking them when they try it again. This leaves the
user blocking themself. Guess what they do next ? ******* ! :-)

I'm assuming that SQL is stuck at some point in the 11 SP's used.

When you cancel a query or stored procedure and SQL rolls back the
transaction, it may take a while to do this. Fair enough, but how does
it handle it if multiple transactions are to be rolled back ? For
example.

A calls B which calls C

A gets cancelled. What happens to B and C ? (assuming A has finished
the call to B but not finished the remainder of the SP i.e B and/or C
are still running).

Is there any way I can identify where it has failed (bit of a long
shot I know)?

Does SQL take the users permissions when running stored procedures
from within other stored procedures, or does it use their permissions
for the first one and SQL Server handles the remainder ? I know the
first one will use the users permissions, but does this carry on
indefinately ?

Any advice / help would be appreciated.

Thanks

Ryan

p.s. SQL 7 running on Windows 2000 Server / Windows 2000 PC's.