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