Mohammad Safyar
Mohammad Safyar

Reputation: 35

error in procedure when use Nested Transactions

/*
DROP TABLE #Temp
DROP TABLE #Inserted
*/
--SELECT * FROM HeapTempLogStep AS htls
--select * FROM TempLogStep AS tls

BEGIN
    SET NOCOUNT ON
    DECLARE @Id bigint ,
    @TrackToken uniqueidentifier ,
    @BusinessKey varchar(50) ,
    @BusinessKeyName varchar(50) ,
    @ActionCode tinyint ,
    @StepCode tinyint ,
    @AspectLevelCode tinyint ,
    @CreationDate datetime2(7) ,
    @BrokerId int,
    @UserId int ,
    @CustomerId int ,
    @IpAddress varchar(50) ,
    @Body Nvarchar(MAX) ,
    @Result varchar(MAX) ,
    @PriorityCode tinyint ,
    @HashCode VARBINARY(MAX),
    @TempLogStepId BIGINT,
    @LastHashCode VARBINARY(MAX),
    @LastLogStepId BIGINT
    
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,Body = [Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    INTO #Temp 
    FROM dbo.HeapTempLogStep 
    ORDER BY Id
    
    CREATE TABLE #Inserted
    (
        Id bigint,
        TrackToken uniqueidentifier
    )
    set XACT_ABORT  ON 
    
    BEGIN TRANSACTION Transport_data_from_Heap
    
    SET @TempLogStepId = ISNULL((SELECT MAX(LastId) FROM dbo.LastRecordTempLogStep),0)
    SET @LastLogStepId = ISNULL((SELECT MAX(Id) FROM dbo.LogStep),0)
    
    IF (@TempLogStepId < @LastLogStepId)
        BEGIN
            IF(@TempLogStepId = 0)
                BEGIN
                    INSERT INTO dbo.LastRecordTempLogStep (LastUpdateTime,LastId) VALUES (sysdatetime(), @LastLogStepId)
                    SET @LastHashCode = 0   
                END
            ELSE
                BEGIN
                    UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @LastLogStepId
                    SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
                END
            SET @TempLogStepId = @LastLogStepId
        END
    ELSE
        BEGIN
            SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM TempLogStep WHERE Id = @TempLogStepId
            IF(LEN(@LastHashCode) <= 0)
            BEGIN
                SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
            END
        END
    
    DECLARE trasport_cursor CURSOR FOR
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,[Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    FROM #Temp
    ORDER BY Id
    
    OPEN trasport_cursor
    
    FETCH NEXT FROM trasport_cursor
    INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
         @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode 
         
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            BEGIN TRAN Insert_Tran
            SAVE TRANSACTION SavePoint1

            SET @HashCode = HASHBYTES('SHA2_256', CONCAT(@LastHashCode , CAST(@ActionCode AS VARCHAR(MAX)) , CAST(@StepCode AS VARCHAR(MAX)) , CAST(@AspectLevelCode AS VARCHAR(MAX)) , CAST(@PriorityCode AS VARCHAR(MAX))
            , CAST(@BrokerId AS VARCHAR(MAX)) , @BusinessKey , @BusinessKeyName , CAST(@CustomerId AS VARCHAR(MAX)) , CAST(@UserId AS VARCHAR(MAX)) , CAST(@IpAddress AS VARCHAR(MAX)) , CAST(@TrackToken AS VARCHAR(MAX)) ,
            (@Body COLLATE SQL_Latin1_General_CP1_CI_AS) , (@Result COLLATE SQL_Latin1_General_CP1_CI_AS))) 
            
            SET @TempLogStepId = @TempLogStepId + 1
            
            INSERT INTO [dbo].[TempLogStep]
            VALUES(@TempLogStepId, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
                   @UserId ,@CustomerId ,@IpAddress ,@Body COLLATE Persian_100_CI_AI ,@Result,sysdatetime() ,@PriorityCode ,CONVERT(VARCHAR(MAX),@HashCode,1))
            PRINT 'successful insert ' + CONVERT(VARCHAR(MAX),@TrackToken)
            INSERT INTO #Inserted
            VALUES (@Id,@TrackToken)
            
            UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @TempLogStepId
            
            SET @LastHashCode = @HashCode   

            COMMIT TRAN Insert_Tran
        END TRY
        BEGIN CATCH
            PRINT 'Get error ' + CONVERT(VARCHAR(MAX),@TrackToken)
            PRINT @@TRANCOUNT
            PRINT XACT_STATE()
            IF ((XACT_STATE() = -1) OR (XACT_STATE() = 1 AND @@TRANCOUNT >= 0))
            BEGIN
                COMMIT TRAN Insert_Tran
                ROLLBACK TRAN SavePoint1
                
                PRINT 'Rollback Succeed ' + CONVERT(VARCHAR(MAX),@TrackToken)
                PRINT @@TRANCOUNT
            END

            FETCH NEXT FROM trasport_cursor
            INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
                 @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode
            
        END CATCH
        
        FETCH NEXT FROM trasport_cursor
        INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
             @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode
    END
    CLOSE trasport_cursor
    DEALLOCATE trasport_cursor
    
    
    
    DELETE A
    FROM dbo.HeapTempLogStep A 
    INNER JOIN #Inserted B ON A.Id = B.Id AND A.TrackToken = B.TrackToken
    
    --RAISERROR('test error',16,1)
    
    PRINT @@TRANCOUNT
    PRINT XACT_STATE()
    IF @@ERROR <> 0
        IF (@@TRANCOUNT > 0)    
            ROLLBACK TRAN
    ELSE IF ((XACT_STATE() = -1) OR (XACT_STATE() = 1 AND @@TRANCOUNT >= 0))
        COMMIT TRAN 
        
    DROP TABLE #Temp
    DROP TABLE #Inserted
END;

i have aprocedure bute error

in Msg 3930, Level 16, State 1, Line 144
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Upvotes: 0

Views: 19

Answers (0)

Related Questions