log table in transaction problem

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

Transact-SQL (2000)

log table in transaction problem

Author

Topic

hadoft
Starting Member

20 Posts

Posted - 2004-03-01 : 08:46:05

i am using a table named TLog to register log info

and I have two stored procedures Proc1, Proc2

each of them while doing its job will insert a log data in table (TLog) which has been prepared to hold all log inserts

the problem is that i want to execute these 2 SP in a single transaction,
and at the end of the transaction i want to be able to read all log data which has been inserted in table TLog from both Prc1 and Prc2 whether the transaction ended with commit OR rollback

the problem is: in ROLLBACK case the inserted log data will be rolled back too, so i cant read them at the end of the execution

the idea is that i want to make some inserts out the influence of the transaction although i am in the scop of the transaction, is it possible ??

any one can give an advice??
please i don't want to give up the TLog because this log method was very very suitable to me.

here is a script that will explain the problem:

/************************* Begin Create Script *********************/
use tempdb
Create table t1
(
a int,
b int,
c int
)
go
Create table TLog
(
msg varchar(50),
No int
)
go
Create procedure Prc1
as
begin

insert into TLog(Msg, No) values ('Begin Prc1', 1);

-- do some job
-- add extra log records to TLog
insert into t1(a, b, c) values( 1, 2, 3);
-- do some job
-- add extra log records to TLog

insert into TLog(Msg, No) values ('End Prc1', 1);

end
go
Create procedure Prc2
as
begin

insert into TLog(Msg, No) values ('Begin Prc2', 1);

begin tran
-- do some job
insert into TLog(Msg, No) values ('log info 1', 1);
insert into TLog(Msg, No) values ('log info 2', 1);
insert into TLog(Msg, No) values ('log info 3', 1);
-- add extra log records to TLog
insert into t1(a, b, c) values( 4, 5, 6);
insert into TLog(Msg, No) values ('log info 4', 1);
insert into TLog(Msg, No) values ('log info 5', 1);
-- do some job

/*for some reason */
rollback;

insert into TLog(Msg, No) values ('End Prc2', 1);

end
go

/************************* End Create Script *********************/
begin transaction
exec prc1
exec prc2
if @@trancount > 0 commit;

-- here i want to get all inserted rows in TLog wheater the transaction has been commited or rollback
select * from TLog
-- but i only ge the rows that has been inserted aftre the tran rollback
go

thanks for your help

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-01 : 08:51:21

Have a look at Save Transaction.

But you might be better putting an identifier on your entries and doing a delete.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hadoft
Starting Member

20 Posts

Posted - 2004-03-01 : 10:05:03

quote:
Originally posted by nr

Have a look at Save Transaction.

But you might be better putting an identifier on your entries and doing a delete.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thanks nr

but trans save point is not exactly what i am looking for, cause it will need a lot of overhead work, and a lot of code to be rechecked

any other ideas??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources