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.
| Author |
Topic |
|
dpais
Yak Posting Veteran
60 Posts |
 Posted - 2008-01-03 : 15:13:40
|
| FOREWORD ::sp_start_job [@job_name = ] 'job_name', [@server_name = ] 'server_name'This will launch a job and immediately return control back to you. The stored procedure will return a 1 for failure and a 0 for successNow that you've started the job you can check on the status by using sp_help_job.sp_help_job [@job_name = ] 'job_name'This will return a whole bunch of columns. The main one you need to be concerned with is current_execution_status. It can have the following values:0 - Returns only those jobs that are not idle or suspended. 1 - Executing. 2 - Waiting for thread. 3 - Between retries. 4 - Idle. 5 - Suspended. 7 - Performing completion actions. QUESTION ::What if I want to start Job 2 only when and if Job 1 is complete and if Job 1 returns 0 for successtherefore the logic would be ......If job1 completed susseffully then run job2 else print "job 2 did not run successfully and the errors are .... "I want to make sure that the jobs run one after another in sequency and make sure that i run the next job only after the previous job is completed SUCCESSFULLY.. there should always be 1 job running at any given time .... but in TSQL.Thanks in advance. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-03 : 15:32:00
|
You can use Return status. The sp_start_job returns 0 for success and 1 for failure.Declare @RetStatus intExec @RetStatus = sp_start_job 'job_name'If @RetStatus = 0 -- do other stuff... Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
|
|
|
Questions on Staggering Jobs