how to persist logging info when rollbacked

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)

how to persist logging info when rollbacked

Author

Topic

besvn
Starting Member

6 Posts

Posted - 2008-01-23 : 10:27:54

I am trying to write a piece of concept code where I do an insert in two tables, and log those inserts to a third table.

My problem exists in the fact that I would like to perform a rollback if the insert doesn't work in one of the table, but like to keep the data in the logging table.

Is there any way to performa a rollback without losing the data in my logging table as well?

Code looks as follows : (just a concept piece, might be syntactic wrong)

Begin transaction

Insert into A values (1)
--log
select @error = @@error

insert into log values (@logmessage)

if @error <> 0
begin
--do something about the error
ROLLBACK transaction
end

--part for B
insert into B value (2)
select @error = @@error

insert into log value (@logmessageB)

if @error <> 0
begin
--do something about the error
ROLLBACK transaction
end

Commit transaction

If the insert in B fails, the rollback also make me to lose the data I inserted in the log table for A, which I want to avoid.

I read about some ways, and one of them was persisting it to a textfile, and going from there to the logtable, but that would be some kind of abusing the system.
Is there any way to persist the logging info, even if a rollback occurred ??

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-01-23 : 12:53:07

Yeah, this is a tough situation.

I wrote something to do this in SQL2K once where I used sp_OACreate to instantiate a Connection to the same database and did my logging through that connection. That was outside the transactions scope so it persisted after rollback. With 2K5, you can probably do something with TRY...CATCH... that would be much simpler.

Jay
to here knows when

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-23 : 17:16:36

You could make use of Table Variables as they are not affected by transactions. For Example:

CREATE TABLE TestTable (ID INT)
CREATE TABLE LogTable (ID INT, LogTime DATETIME)

DECLARE @LogTable TABLE (ID INT)

BEGIN TRY

	BEGIN TRANSACTION

	INSERT @LogTable SELECT 1
	INSERT TestTable SELECT 1

	RAISERROR('Throwing an Error', 16, 1)
	
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Rollback and log Error
	ROLLBACK TRANSACTION

	INSERT LogTable SELECT ID, GETDATE() FROM @LogTable
END CATCH

SELECT * FROM TestTable
SELECT * FROM LogTable

DROP TABLE TestTable
DROP TABLE LogTable

EDIT: Had the @LogTable and TestTable inserted backwards.

besvn
Starting Member

6 Posts

Posted - 2008-01-24 : 03:16:04

Hmm this last one I didn't know yet. By using a variable to hold the table, it doesn't get rollbacked.
This will be very usefull in SQLServer. Thank you very much for the information!

I still have a question though :

Is it possible to pass this table variable to a subprocedure?
I mean, create the table variable in the parent procedure (and persist it there in the end) but add logging/records to it in the subprocedure. This might be more useful, because I have to declare the variable only once then, i.s.o over and over again in each procedure (and persist it each time per procedure).

I guess it works, but what is the syntax to pass it?

besvn
Starting Member

6 Posts

Posted - 2008-01-24 : 05:20:23

I read about the passing of the table variable. It is via converting the table to XML, and pass the XML as such.
THe above solutions also mean that every procedure has to be adapted (every call to the logging procedure is to be changed to an insert in the logging table)
I am going to investigate the 'extra connection' part as well, as it looks that I have to change only one piece of code then. (the logging procedure)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources