CODEWITHSUNDEEP
sqlsql-servert-sqlsqlcmd
Gabor
Gabor

Reputation: 179

Inner rollback transaction rolls back the outer too

I faced a problem like this. I have this transaction, and $(FilePath) specifies another script, that this should start running.

BEGIN TRANSACTION
:r $(FilePath)
GO
IF(@@ERROR <> 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
ELSE
    BEGIN
        COMMIT TRANSACTION
    END

(Note that the scripts that are called by the sqlcmd mostly doesn't contain transacions) The problem is that, if the script that is being called contains a rollback transaction then it rolls back the outer transaction too. The inner scripts doesn't contain named transactions, and there are way too many scripts to rewrite each transaction to be named.

Is there a way to make this transaction only roll back if the corresponding rollback transaction runs?

Thank you

Upvotes: 1

Views: 235

Answers (1)

NickyvV
NickyvV

Reputation: 1746

Try using a savepoint_name with your ROLLBACK statement like described here:
Without this savepoint the ROLLBACK statement rolls back transactions to the outermost BEGIN TRANSACTION statement as designed.

ROLLBACK { TRAN | TRANSACTION } 
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ] 
[ ; ]

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

Upvotes: 4

Related Questions