September 23, 2003 at 6:09 am
Hello,
I am looking to check if a particular job is running and if it is running(over a period of time) I want to stop it dynamically.
SP_Help_Job gives me the details, but I need to get the @execution_status parameter as output and use it to stop the job. Is there any other way?
Thanks
September 23, 2003 at 6:31 pm
Try this,
SELECT a.name, a.current_execution_status
FROM OPENROWSET('SQLOLEDB','<your server>';'< sql login >';',sql password>',
'SET FMTONLY OFF EXEC msdb..sp_help_job') AS a
WHERE a.current_execution_status = 1
This will not work if your server is setup for windows authentication. If that is the case, write a stored procedure that performs much the same function as sp_help_job.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 24, 2003 at 4:40 am
Thanks very much. This is much quicker way. I actually ended up having a temp table (same structure as 'XP_sqlagent_enum_jobs' return values) and inserted the data from 'XP_sqlagent_enum_jobs' into it. I used the 'Running' column value for it. Its in a stored procedure now, so as you suggested it might work for 'windows authentication mode'. Thanks again.
Regards
Narayan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply