Comit and Roll Back - 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

Transact-SQL (2000)

Comit and Roll Back

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 14:45:40

when I use a query in query analzser with comit and roll back.it would wait till I close the analzer and it will ask whether I want to commit or am I rolling back.This the code

Begin tran
update tbl_emp_details
set activation_flag = 5 ,
activation_process_name=T.activation_process_name,
activation_send_date =getdate()
from tbl_emp_details E join tbl_condition T on
TD.emp_id=TC.emp_id

if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end

commit tran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 14:50:34

You do not need to use a transaction with only one statement. It's when you use multiple DML statements that you need a transaction.


if @@error <> 0
begin
  rollback tran
  raiserror ('failed', 16, -1)
  return -1
end
else
begin
  commit tran
  return 0
end

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 15:23:25

Well...it's a sproc isn't it...can't use a return in a script...

If it is a script it will automatically rollback the changes unless there an BEGIN TRAN open in your session...

And I think if you close a qa connection with an open tran, it'll try and commit it...

I'll check it out....

Brett

8-)

EDIT:

Damn: it propmpts you to commit or not...coool....

Better off managing everything yourself though...

So, sproc or script?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 15:24:38

The problem with his code is there is no commit tran in it. He rolls it back on error, but doesn't commit it if it was successfull. So QA asks if that's what you want to do when you try to close the window with a transaction still open.

And yes I assumed it was in a sproc cuz it would have given an error as soon as you tried to run it with RETURN.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 15:35:43

[code]
Begin tran
update tbl_emp_details
set activation_flag = 5 ,
activation_process_name=T.activation_process_name,
activation_send_date =getdate()
from tbl_emp_details E join tbl_condition T on
TD.emp_id=TC.emp_id

if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end

commit tran
[/code]

It's there....had to look twice...

And with the looks of the code, you'd have begins, commits, rollbackes, raise all over your code...

This needs somework...but it shows you I structure my code...

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 15:38:55

I didn't see it down there.

sqllearner, could you post the real code for us?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources