February 11, 2011 at 2:49 am
I want to find all the scheduled jobs using a query in MSSQL Server. can anybody help me.
Thanks
- Venkat
February 11, 2011 at 3:06 am
use msdb
go
select a.name as Jobname, a.enabled,
b.next_run_Date as [Next Schedule to Run],
b.next_run_time as [Scheduled time to execute]
from sysjobs a, sysjobschedules b
where a.job_id= b.job_id
This query will give you information on the jobs which are scheduled.
But you can explore sysjobs to get list of all the jobs name(either disabled/enabled)
----------
Ashish
February 11, 2011 at 3:08 am
Have you tried sp_help_job stored procedure?
February 11, 2011 at 3:18 am
@Ashish.. thanks for the query.,
yes.. i've gone through "sp_help_job" procedure.... pretty useful..
Thank you guys...!!
February 11, 2011 at 5:16 am
Regarding...sp_help_job.
i would like to know, how can i process the result set which sp_help_job returns...??
select name, case when current_execution_status = 1 then 'Executing'
when current_execution_status = 2 then 'Waiting For Thread'
when current_execution_status = 3 then 'Between Retries'
when current_execution_status = 4 then 'Idle'
when current_execution_status = 5 then 'Suspended'
when current_execution_status = 6 then '[Obsolete]'
when current_execution_status = 7 then 'PerformingCompletionActions'
else NULL end as [status] from
openrowset(' ', 'Server=(local);Trusted_Connection=yes;','EXEC msdb.sp_help_job')
i saw that, Using the above query, its possible to access the values, which the sp_help_job procedure returns,, but i have a problem here, in the openrowset() function, i'm not able to find the value for first parameter,(OLE DB provider_name)
kindly reply.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply