CODEWITHSUNDEEP
sqlsql-server-2008t-sqltransactions
unj2
unj2

Reputation: 53491

Rollback of nested transaction throwing error in TSQL

Here is a snippet of what I am trying to achieve. I have a nested transaction 'tran2', which I can't rollback 

SELECT 'Before', * FROM [table] WHERE field ..

BEGIN TRAN tran1
UPDATE [table] set field ... WHERE field ..

 BEGIN TRAN tran2
    SELECT 'During', * FROM [table] WHERE field ..
    select @@trancount as 'transactioncount'

  rollback tran tran2

rollback TRAN tran1
SELECT 'After', * FROM [table] WHERE field ..

is throwing me this error

Msg 6401, Level 16, State 1, Line 13
Cannot roll back tran2. No transaction or savepoint of that name was found.

however the @@trancount is giving me 2 transactions at that point

Upvotes: 2

Views: 5287

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103467

Instead of begin trans tran2, use save transaction tran2.

This creates a save point within the outer transaction, which you can rollback to.

Upvotes: 3

Related Questions