Reputation: 35
/*
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