restart job automatically

[smagaz]

I have create a job that export the DB to Access and sometimes the job hang up. giving this message in the event viewer:
"SQL Server Scheduled Job 'Call_Prod' (0xA8C952D21CF4B444B8D1D1AAC774986D) - Status: Failed - Invoked on: 2005-03-10 16:20:00 - Message: The job failed. The Job was invoked by Schedule 5 (Call_Prod). The last step to run was step 1 (Call_Prod). "

Then I have to stop it and start it again.
I'd like to know if it is possible to detect when the job is not complete and restart it automatically.
Is it possible ?
thanks and regards
Salva

 

[mrdenny]

You should need to have a second job, check on the status of the job, and have that job stop and start the first job.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[smagaz]

Thank you for your suggestion Denny.
As I am new in sql Server can you tell me how to create this second job and specify to stop and start another job
thanks a lot
Salva

 

[Catadmin]

sp_Start_job and sp_stop_job are system stored procedures that have to be used in the context of the MSDB database.

I'm not sure on the checking the status of the first job, but look up Job in Books Online. Then create a T-SQL query that checks the job, uses an If Then statement to stop and restart the job using the above stored procedures.

Also look up If / Then in Books Online and the stored procedures I listed above.

I posted a thread in the SQL Server: Programming forum with some code I wrote to disable a job if a restore failed that you could check out. The title is "Disable scheduled job.." It might give you an idea on how to do your error checking. Though, you'll want to ignore the Restore Log part of it. @=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!