CODEWITHSUNDEEP
sqlsql-serverstored-procedures
RXC
RXC

Reputation: 1233

SQL - Transaction count after EXECUTE indicates a mismatch

I am working on a stored procedure in MS SQL Server Management Studio 2012.

I am reusing some code from other procedures and this includes calling another stored procedure with the EXEC command.

This procedure is going to take information for an object as parameters, the reset that object's status, and then look for related (downstream) objects and reset their statuses as well.

The code was working but required a small change to reset and related object's status even if it was not in an error status. To address this issue, I created a variable before the main loop that iterates over the related objects, and then when the first object that has an error status is encountered, it sets the value of the variable. The variable itself is a BIT.

Here is the procedure:

USE [DB_TEST]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER          PROC [dbo].[resetTasksInErrorStatus]
    @TaskId                         INT,
    @SubjectId                      INT,
    @ProjectProtocolId              INT,
    @TimePointId                    INT,
    @WorkbookId                     INT,
    @LogCreatorUserId               INT,
    @LogReasonTypeId                INT,
    @LogOtherReason                 VARCHAR(256),
    @SignatureCaptured              BIT,
    @ManualChangeTaskStatus         BIT,
    @ErrorIfModifiedAfter           DATETIME,       
    @NewTaskStatusID                INT,            
    @ProcessorUserId                INT

AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION ResetTasksInError

    -- Make sure the task has not been modified since the reset was requested
    IF(EXISTS(
        SELECT ti.* FROM TaskInstance as ti
        WHERE ti.ModifyDate > @ErrorIfModifiedAfter
            AND ti.TaskId = @TaskId
            AND ti.SubjectId = @SubjectId
            AND ti.ProjectProtocolId = @ProjectProtocolId
            AND ti.TimePointId = @TimePointId
            AND ti.WorkbookId = @WorkbookId))
    BEGIN
        RAISERROR('The task to be reset was modified before the reset could complete', 16, 1);
        GOTO error
    END

    -- Get all downstream task instances
    SELECT *
    INTO #downstreamTaskInstances
    from dbo.fnGetTaskInstancesDownstream
    (
        @TaskId,
        @SubjectId,
        @TimePointId
    )

    -- Get the previous task status
    DECLARE @OldTaskStatus INT;
    SELECT TOP 1 @OldTaskStatus = ti.TaskStatusTypeId FROM TaskInstance as ti
    WHERE ti.TaskId = @TaskId
        AND ti.SubjectId = @SubjectId
        AND ti.TimePointId = @TimePointId

    -- Reset the task
    EXEC setTaskStatus
        @TaskID,
        @SubjectId,
        @ProjectProtocolId,
        @TimePointId,
        @WorkBookId,
        @OldTaskStatus,
        @NewTaskStatusID,
        @ProcessorUserId,
        @LogCreatorUserId,
        @LogReasonTypeId,
        @LogOtherReason,
        @SignatureCaptured,
        @ManualChangeTaskStatus,
        NULL,
        NULL,
        NULL,
        1

    -- Check if setTaskStatus rolled back our transaction
    IF(@@TRANCOUNT = 0)
    BEGIN
        RAISERROR('Error in sub procedure. Changes rolled back.', 16, 1);
        RETURN
    END

    -- Set a boolean variable to determine whether downstream tasks should be reset
    DECLARE @ResetDownstreamTasks BIT = 0;
    --Set @ResetDownstreamTasks = 0;

    -- Create a cursor of the downstream tasks
    DECLARE downstreamCursor CURSOR FOR
        SELECT TaskId, TimePointId, ProcessorUserId, TaskStatus, WorkBookId, ProjectProtocolId, IsManual, TaskStatus
        FROM #downstreamTaskInstances
    OPEN downstreamCursor

    -- Reset each downstream task to unprocessed
    DECLARE @CursorTaskID INT;
    DECLARE @CursorTimePointID INT;
    DECLARE @CursorProcessorUserId INT;
    DECLARE @CursorTaskStatusID INT;
    DECLARE @CursorWorkBookId INT;
    DECLARE @CursorProjectProtocolId INT;
    DECLARE @CursorIsManual BIT;
    Declare @CursorTaskStatus INT;
    FETCH NEXT FROM downstreamCursor INTO @CursorTaskID, @CursorTimePointId, @CursorProcessorUserId, @CursorTaskStatusID, @CursorWorkBookId, @CursorProjectProtocolId, @CursorIsManual, @CursorTaskStatus
    WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0 AND @@TRANCOUNT = 1
    BEGIN
        -- Check if the task is in error status, and then make sure it is not an manual task.
        -- Manual tasks should never be in error status, so there is no need to reset them.
        if @CursorTaskStatus = 10 and @CursorIsManual <> 1
        begin
                SET @ResetDownstreamTasks = 1;

                EXEC setTaskStatus
                    @CursorTaskID,
                    @SubjectId,
                    @CursorProjectProtocolId,
                    @CursorTimePointId,
                    @CursorWorkBookId,
                    @CursorTaskStatusID,
                    1, -- Unprocessed
                    @CursorProcessorUserId,
                    @LogCreatorUserId,
                    @LogReasonTypeId,
                    @LogOtherReason,
                    @SignatureCaptured,
                    @ManualChangeTaskStatus,
                    NULL,
                    NULL,
                    NULL,
                    0
        end;


        if @ResetDownstreamTasks = 1
        begin
            EXEC setTaskStatus
                @CursorTaskID,
                @SubjectId,
                @CursorProjectProtocolId,
                @CursorTimePointId,
                @CursorWorkBookId,
                @CursorTaskStatusID,
                6, -- Inspected
                @CursorProcessorUserId,
                @LogCreatorUserId,
                @LogReasonTypeId,
                @LogOtherReason,
                @SignatureCaptured,
                @ManualChangeTaskStatus,
                NULL,
                NULL,
                NULL,
                0
        end

      FETCH NEXT FROM downstreamCursor INTO @CursorTaskID, @CursorTimePointId, @CursorProcessorUserId, @CursorTaskStatusID, @CursorWorkBookId, @CursorProjectProtocolId, @CursorIsManual, @CursorTaskStatus
    END

    DROP TABLE #downstreamTaskInstances
    CLOSE downstreamCursor
    DEALLOCATE downstreamCursor

    -- Check if setTaskStatus rolled back our transaction
    IF(@@TRANCOUNT = 0)
    BEGIN
        RAISERROR('Error in sub procedure. Changes rolled back.', 16, 1);
        RETURN
    END

    IF(@@ERROR <> 0)
    BEGIN
        GOTO ERROR
    END

    COMMIT TRANSACTION  ResetTasksInError
    RETURN

    ERROR:
        RAISERROR('Error encountered. Changes rolled back.', 16,1);
        ROLLBACK TRANSACTION ResetTasksInError
        RETURN
END

When I run the procedure I get these errors:

Msg 266, Level 16, State 2, Procedure setTaskStatus, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Msg 16943, Level 16, State 4, Procedure resetTasksInErrorStatus, Line 197
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 3701, Level 11, State 5, Procedure resetTasksInErrorStatus, Line 200
Cannot drop the table '#downstreamTaskInstances', because it does not exist or you do not have permission.
Msg 50000, Level 16, State 1, Procedure resetTasksInErrorStatus, Line 207
Error in sub procedure. Changes rolled back.

If I comment out the SET ... statement the procedure runs and works (not as desired).

I have looked around for similar questions, but none of them solved my problem.

Is there something I am missing with regards to the SET statement?

Is it affecting the @@TRANCOUNT variable somehow?

I did see some posts around that mentioned the problem is likely to be in the stored procedure that this one calls, but I am a little hesitant because that stored procedure works, and these errors only show up when trying to set the value of the variable.

Upvotes: 0

Views: 1607

Answers (2)

RXC
RXC

Reputation: 1233

I ended finding a way to make the stored procedure work.

What I did was I removed the stored procedure EXEC statement from the if statement that set the value of the variable.

So the code in the loop looks like this:

WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0 AND @@TRANCOUNT = 1
    BEGIN
        -- Check if the task is in error status, and then make sure it is not an manual task.
        -- Manual tasks should never be in error status, so there is no need to reset them.
        if @CursorTaskStatus = 10 and @CursorIsManual <> 1
        begin
                SET @ResetDownstreamTasks = 1;
        end;


        if @ResetDownstreamTasks = 1
        begin
            EXEC setTaskStatus
                @CursorTaskID,
                @SubjectId,
                @CursorProjectProtocolId,
                @CursorTimePointId,
                @CursorWorkBookId,
                @CursorTaskStatusID,
                6, -- Inspected
                @CursorProcessorUserId,
                @LogCreatorUserId,
                @LogReasonTypeId,
                @LogOtherReason,
                @SignatureCaptured,
                @ManualChangeTaskStatus,
                NULL,
                NULL,
                NULL,
                0
        end

      FETCH NEXT FROM downstreamCursor INTO @CursorTaskID, @CursorTimePointId, @CursorProcessorUserId, @CursorTaskStatusID, @CursorWorkBookId, @CursorProjectProtocolId, @CursorIsManual, @CursorTaskStatus
    END

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Just a guess .. but i think that what happens is the following:

  1. you create a temp table inside the transaction.
  2. you start a cursor and iterate through it until one of the inner sps gives error.
  3. the transaction is rolledback so the temp table will go with the transaction
  4. the cursor gives the schema change error
  5. drop error

So you should check for the error in one of the inner sps.

Hope it helps.

Upvotes: 1

Related Questions