Nested Transactions - 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 2005 Forums

Transact-SQL (2005)

Nested Transactions

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-02 : 19:41:13

I have a stored procedure(sp_log_error) which basically logs in an error log table. This stored procedure has its own begin trans and commit / rollback trans statements.

This procedure is called by various other procedures to log the error messages, the problem is when I roll back the parent procedure any changes made by sp_log_error is also rolled back. Any way I can avoid it?

Thanks
Karunakaran

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-02 : 20:18:51

Do you need to put this proc in a transaction if you are just logging errors?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-02 : 20:39:51

quote:
Originally posted by dinakar

Do you need to put this proc in a transaction if you are just logging errors?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

I'm assuming you are asking about the transaction inside the error logging sp, If that is the case, as long as I can log the errors I can take out the transaction from the error logging sp.

Thanks
Karunakaran

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-03 : 13:49:28

If you call your error logging proc from inside the parent tansaction and then roll it back it will rollback whatever was done in the child proc. For example:

CREATE TABLE #Temp (ID INT)

INSERT #Temp
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3

BEGIN TRANSACTION

	UPDATE #Temp
	SET ID = 10
	WHERE ID = 3

	BEGIN TRANSACTION
		INSERT #Temp
		SELECT 99
	COMMIT TRANSACTION

ROLLBACK TRANSACTION

SELECT *
FROM @Temp

So in order to avoid the error logging from rolling back you would need to do the rollback, then log the error. Hopefully, that makes sense.

-Ryan

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-03 : 13:53:53

Just take the transaction out of the logging sproc, and make sure the logging call is also outside of any other transaction as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources