Nested Transaction - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Nested Transaction

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-07 : 08:55:53

David writes "Hello,
I do want to commit Inner transaction in sql server, when it is part of outer transaction. IF outer transaction is roll back still i do want to commit inner transaction. How can i do that. Pls let me know.

MY code is as follows

BEGIN Transaction

Insert into Table1
(Id,Name)
values
(1,'test'
)

IF @@error <> 0
BEGIN
BEGIN Transaction2
Insert into logtable
(ErrorId)
values
(@@error
)
COMMIT Transaction2
END

IF @@error <> 0
Rollback Transaction1
Else
Commit Transaction1
..so pls let me know

Thanks"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-07 : 09:05:34

Here's a really neat article describing a way to log errors within a transaction that might be rolled back:

http://www.sqlteam.com/item.asp?ItemID=2290

Alternately, you can use a very basic text file method to log errors to a file, then read the file later:

DECLARE @error varchar(255)
BEGIN TRAN first
...code
SET @error='@echo ' + CAST(@@ERROR AS varchar) + ' >>c:\logfile.txt'
EXEC master..xp_cmdshell @error
...code
...ROLLBACK TRAN or COMMIT TRAN

The call to xp_cmdshell isn't logged since it operates outside of the SQL Server process. The log entries will be stored in the file logfile.txt by using the ECHO command. Now you only need one transaction, and even if it rolls back the file contains the errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources