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.

Ryan (ryanofford@hotmail.com) writes:
>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).

When you have nested transaction, all is in fact one big transaction.
The inner BEGIN and COMMIT TRANSACTION only increase and decrease a
transaction counter. When you ROLLBACK, you rollback to the outermost
BEGIN TRANSACTION.

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

With some SELECT with NOLOCK and knowledge about the procedures, you
might be able to dig out something.

But if the problem really is long-running ROLLBACK, you should see
this with sp_who or sp_who2. Another possibility is that when the
users press CTRL-ALT-DEL and close the application, that there is
some DLL or whatever which is still alive, so there is no rollback.

>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 ?

If user A calls a procedure dbo.a_sp which calls dbo.b_sp, the all the
way in the procedures, the permissions to the objects are those of
dbo. Unless, that is, there is no dynamic SQL in the procedures. For
dynamic SQL, the user's own permissions apply.
--
Erland Sommarskog, Stockholm, sommar@algonet.se