November 7, 2003 at 1:08 pm
When I cancel the execution of procedure (not killing the SPID) should it not rollback?
I have the following proc, so that I transfer few records at a time
from one table to other, without filling the log. When I cancel in the middle of
execution of the following proc, often it holds locks on Status table.
The idea is that I can cancel in the middle of the procedure and when I kick off,
it should pick from where it had left off (the status table tracks the progress).
When I identify the process id and kill it, it rolls back and the Status table is relased.
Isn't canceling in the middle of execution the same as killing the process?
BTW, after cancel, when I do @@Trancount, it shows 2, 3 something like that.
It looks like cancelling returns immediately, without cleanup? Right?
WHILE (Some condition)
BEGIN
BEGIN TRAN
-- the table names are dynamic
SET @ExecString = 'Insert Clause'
EXEC sp_executesql @ExecString,
N'@error INT OUTPUT, @numrows INT OUTPUT',
@ErrCode OUTPUT, @numrows OUTPUT
IF @ErrCode <> 0
BEGIN
ROLLBACK TRAN
SELECT @message = description
FROM master..sysmessages
WHERE error = @ErrCode
END
ELSE
BEGIN
COMMIT TRAN
END
INSERT INTO dbo.Status (RecordDate,
StartTime,
EndTime,
Duration,
NumRows,
Status,
LastMessage)
SELECT @ProcessDate,
@startdate,
@enddate,
datediff (ss, @startdate, @enddate),
@numrows,
@ErrCode,
@message
<Increment the variable for while loop>
END
November 7, 2003 at 2:28 pm
Canceling vs kill is different. Kill command rolls back where as cancelling would not rollback unless explicitely specified and the rollback meets the criterea that's explicitely coded.
November 7, 2003 at 3:35 pm
Thank you. That supports the additional testing I did. Do you know how to
explicitly code for ROLLBACK if the proc execution is cancelled?
thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply