Tyler Hudson (TylerH@Spam.MeNOTallpax.com) writes:
> So let's say the client disconnects after issuing the cancel command.
> Would SQL server roll back the commands executed before the cancel
> command?

Uncommitted transactions would be rolled back. But anything that already
has been committed will of course not be rolled back. Say that the user
submits a call to a stored procedures that performs this:

BEGIN TRANSACTION A
-- do some stuff
COMMIT TRANSACTION A

BEGIN TRANSACTION B
-- do some stuff
COMMIT TRANSACTION B

UPDATE tbl SET ...

BEGIN TRANSACTION C
-- do some stuff
BEGIN TRANSACTION D
-- do some other stuff
COMMIT TRANSACTION D
-- do even more stuff
COMMIT TRANSACTION C

Say now that the timeout sets in while transaction C is in progress, and
the client then disconnects. Transactions A, B and the UPDATE statement
will remain, but there will be nothing of transaction C. Note that this
applies even if COMMIT TRANSACTION D has been committed, because COMMIT
for a nested transaction only decrements the transaction level. Nothing
is committed.

If the timeout sets in while the UPDATE command is running, transaction
A and B will remain, but there will be no trace of the UPDATE statement.
(And of course not of transactions C and D.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp