Nested transactions in SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested transactions in SQL Server 2005

Author

Topic

raviborra
Starting Member

14 Posts

Posted - 2008-10-06 : 08:20:32

Hi,

Can some one help me to create nested transactions as follows -

BEGIN TRY
BEGIN TRANSACTION LEVEL1
BEGIN TRY
BEGIN TRANSACTION LEVEL2
RAISERROR('LEVEL2',16,1);
COMMIT TRANSACTION LEVEL2
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION LEVEL2
PRINT 'I AM IN LEVEL2 OF CATCH'
END CATCH
RAISERROR('LEVEL1',16,1);
COMMIT TRANSACTION LEVEL1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION LEVEL2
PRINT 'I AM IN LEVEL1 OF CATCH'
END CATCH

Specified code doesn't work, but i want to use nested transaction like that.

Thanks in advance,
Ravi.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 08:23:29

quote:
Originally posted by raviborra

Hi,

Can some one help me to create nested transactions as follows -

BEGIN TRY
BEGIN TRANSACTION LEVEL1
BEGIN TRY
BEGIN TRANSACTION LEVEL2
RAISERROR('LEVEL2',16,1);
COMMIT TRANSACTION LEVEL2
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION LEVEL2
PRINT 'I AM IN LEVEL2 OF CATCH'
END CATCH
RAISERROR('LEVEL1',16,1);
COMMIT TRANSACTION LEVEL1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION ~~LEVEL2~~ LEVEL1
PRINT 'I AM IN LEVEL1 OF CATCH'
END CATCH

Specified code doesn't work, but i want to use nested transaction like that.

Thanks in advance,
Ravi.

raviborra
Starting Member

14 Posts

Posted - 2008-10-06 : 08:44:04

Hi,

Even though it is

BEGIN TRY
BEGIN TRANSACTION LEVEL1
BEGIN TRY
BEGIN TRANSACTION LEVEL2
RAISERROR('LEVEL2',16,1);
COMMIT TRANSACTION LEVEL2
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION LEVEL2
PRINT 'I AM IN LEVEL2 OF CATCH'
END CATCH
RAISERROR('LEVEL1',16,1);
COMMIT TRANSACTION LEVEL1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION LEVEL1
PRINT 'I AM IN LEVEL1 OF CATCH'
END CATCH

It does not working. It gives error in the level two catch block that "Cannot roll back LEVEL2. No transaction or savepoint of that name was found."

Thanks,
Ravi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-06 : 12:36:09

The code you posed above is working for me. Do you have XACT_ABORT set to ON? (EDIT: I just tried with XACT_ABORT ON and it didn't affect the results).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources