|
|
|
Author |
Message |
advisor
|
Posted: Thu Oct 16 12:05:43 CDT 2003 |
Top |
SQL Server Developer >> Stopping a hung job
OK, I have looked through 6 different TSQL book, MSDN, and Technet. I know this must be really basic stuff, but I can't figure out a solution to my problem:
I have a Job that runs once an hour to append data to table from an Access DB. I works wonderfully, but once in a while, if the Access DB is not online or something, the job hangs and I have to manually stop it. The job takes less than 1 minute to run, so I would like to automatically check the job after ~ 5 minutes to see if it is still running, and if it is, stop and restart it.
The one solution I can see is running another job 5 minutes after the previous job is scheduled. This job can use the Stored Procedure sp_help_job to retreive the current_execution_status of the job. My problem is I have no clue how to retrieve only the current_execution_status row from the Stored Procedure and use an IF THEN statement to stop and restart the job.
SQL Server295
|
|
|
|
|
Aaron
|
Posted: Thu Oct 16 12:05:43 CDT 2003 |
Top |
SQL Server Developer >> Stopping a hung job
> current_execution_status of the job. My problem is I have no clue how to
retrieve only the current_execution_status row from the Stored Procedure and
use an IF THEN statement to stop and restart the job.
There's some code that works along these lines here:
http://www.aspfaq.com/2194
It creates a temp table, and inserts the result of xp_sqlagent_enum_jobs
into it. It is currently making sure it isn't already running before
starting it again, using sp_Start_job, but you could easily use that same
logic to stop and start the job if it's still running.
A
|
|
|
|
|
|
|