Creating a sql job

Question

Creating a sql job

rinashc

SSC Rookie

Points: 36
More actions
August 17, 2009 at 2:55 am

#134866

Hi
I have created a simple job that updates a table
below is the step that the job will execute
eg
begin tran
update table1
set column1 = 'test'
where staffid in ( select staffid from tb_staff)
commit tran
what i would like to know whether it is necesarry to perform error handling within the job if the update statement fails
like @error <> 0
regards
rc

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It depends on what you have to do with errors. If you want to perform some particular action in case of error, you can hanlde it directly in the sql code, or you can set up a conditional branching to another step, based on the "on success action" / "on failure action".

Regards

Gianluca

-- Gianluca Sartori

rinashc SSC Rookie Points: 36 More actions · Answer 2

thanks for the reply Gianluca.

rollback the transaction if the update fails would be the best thing i guess i could handle that in the code.

ps - Italy played really bad in the confed cup they should have brought del piero down 🙂

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

If you have to rollback the transaction you will have to do it in the same job step.

Italy played really bad in the confederations cup, but I didn't find it surprising. They played really bad in the World Cup as well to be honest.

Del Piero is a good player, but he's a bit too old. Why not calling Paolo Rossi instead? He's just 53... 🙂

-- Gianluca Sartori

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

In your case its not needed as you are specifying it in begin tran and commit tran .So update will roll back and SQL will internally throw the correct error .

Generally we use :

IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

and then at last we use :

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

But we use this in case we run a query under exec or under Begin and End ..

So you are safe man...

tc

Abhay

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

pavel0444i SSC Enthusiast Points: 116 More actions · Answer 5

Thank you for the thread. I thought it is a real sql Job. I mean you want to hire someone

Buy ab circle pro [/url]

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 6

pavel0444i (8/21/2009)
Thank you for the thread. I thought it is a real sql Job. I mean you want to hire someone
Buy ab circle pro [/url]

That is funny 🙂