Rollback transaction multiple query

Question

I have a store procedure like below:

DECLARE @FinalQuery varchar(max), @TableName varchar(200)
DECLARE ListTable CURSOR FOR SELECT....
DECLARE @isError bit=0
OPEN ListTable 
FETCH NEXT FROM ListTable INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
    //Some code to generate @FinalQuery to merge data into table @TableName
    BEGIN TRY
        EXEC(@FinalQuery)
    END TRY
    BEGIN CATCH
        Set @IsError = 1
        //Some code to write error to log file.
    END CATCH
    FETCH NEXT FROM ListTable INTO @TableName
END
CLOSE ListTable 
DEALLOCATE ListTable

Now, I want to add transaction and rollback to my store procedure. I want all dynamic @FinalQuery query to be executed, and finally, when it finish, if any error exists ( @IsError=1) then rollback everything. How can i do that ?

glaeran · Answer 1 · 2014-04-15T10:54:01.223

0

 BEGIN TRAN
 BEGIN TRY
   EXEC(@FinalQuery)
END TRY
BEGIN CATCH
    Set @IsError = 1
    //Some code to write error to log file.
ROLLBACK
END CATCH
COMMIT

edited Apr 15 '14 at 10:54

answered Apr 15 '14 at 10:37

glaeran

410
4
14

I got this error: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. – user2500561 Apr 15 '14 at 10:42
Different error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. – user2500561 Apr 15 '14 at 10:50
Last try, if not then I have no idea how to help :) – glaeran Apr 15 '14 at 10:54
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction again. Thank you anyway :) – user2500561 Apr 15 '14 at 10:57
http://www.sqlservercentral.com/Forums/Topic1170215-146-1.aspx Check this link maybe will be somehow helpful – glaeran Apr 15 '14 at 10:59

score 0 · Answer 2 · answered Apr 15 '14 at 11:00

As you want all the changes to be rolled back in the event of an error, have you tried moving the transaction out of the loop?

OPEN ListTable 
FETCH NEXT FROM ListTable INTO @TableName
BEGIN TRY
    WHILE @@FETCH_STATUS=0
    BEGIN
        //Some code to generate @FinalQuery to merge data into table @TableName
        EXEC(@FinalQuery)
        FETCH NEXT FROM ListTable INTO @TableName
    END
    COMMIT
END TRY
BEGIN CATCH
    Set @IsError = 1
    //Some code to write error to log file.
    ROLLBACK
END CATCH
CLOSE ListTable 
DEALLOCATE ListTable

Rollback transaction multiple query

2 Answers2