Transactions problem...

Question

Transactions problem...

Nuno

SSC-Addicted

Points: 428
More actions
December 4, 2003 at 10:48 am

#176961

Hi,
I have a sproc:
begin tran
while <condition>
update sometable
if @@error <> 0
rollback tran
update TmpTable
end
commit tran
I would like to know if is there a way of making only the second update to
commit before the commit tran at the end is reached.
Thanks in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nuno SSC-Addicted Points: 428 More actions · Answer 1

I need to do that because i'm writing a vb app that monitors the sproc progression by reading it on the TmpTable of the example given.

I would really apreciate any reply, even if it's to tell me that it can not be done.

Thanks

cmore SSCrazy Points: 2452 More actions · Answer 2

I usually specifically keep any logging actions I am doing outside of transactions - if the transaction is rolled back you lose your log of what was going on.

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Depending on how long your log data is, you could save the logging data to a local string or output to a text file, then after the rollback, repopulate the log table.

Once you understand the BITs, all the pieces come together

cmore SSCrazy Points: 2452 More actions · Answer 4

tsql help -> save transaction -> transaction savepoints

would be one way of keeping your logging in the same transaction yet allow you to roll back what didn't work. I doubt this is what you are looking for though, because if your intent is to retain the atomicity of the transaction as a whole (not counting the log) then you won't want to commit any of what has happened - which will include your log records.

This is the only thing I am aware of that allows manipulation of what is committed within a begin/commit.

how about





insert into MyLog (TxtMsg) values ('Beginning looped update')



begin tran

while <condition>

begin

  set @IDColOfUpdateAttemptData = x

  update sometable where IDCOl = @IDColOfUpdateAttemptData 

  if @@error <> 0 

  begin

    rollback tran

    goto ErrOut

  end

  set @IDColOfUpdateAttemptData = x

end



Finish:

commit tran

return 0



ErrOut:

insert into MyLog (TxtMsg) values ('failed insertion on IDCol:'+ltrim(str(@)))

return -1

Edited by - cmore on 12/05/2003 08:22:58 AM

Nuno SSC-Addicted Points: 428 More actions · Answer 5

Thanks cmore and ThomasH for your replies.

My idea is to use the one field on tmptable (int) to show the sproc progression in a visual basic progress bar, that's why it must be inside the transaction.

I will test writing the data to a text file like ThomasH suggested and see the effect on the sproc performance.