Cancel Execution V/S Kill Process - Transaction

  • 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

  • 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.

  • 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