471,071 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

Recurring Scheduled Job

Is it possible to display when a scheduled job is next due to run in
either a view or a table?
If so, how?
thanks

Bryan

Sep 13 '06 #1
3 1797
This is for SQL 2000. Running msdb..sp_help_job will display the
information. I dug through the msdb..sp_help_jobschedule and put the
following query together:

select name
,cast(left(cast(next_run_date as varchar),4)+'-'+
substring(cast(next_run_date as
varchar),5,2)+'-'+right(cast(next_run_date as varchar),2) +' '+
left(cast(next_run_time as varchar),2)+':'+substring(cast(next_run_time
as varchar),3,2)+':'+right(cast(next_run_time as varchar),2) as
datetime) "Next_Run"
from msdb..sysjobschedules

Converting the next_run_date and next_run_time columns got kind of
messy, but it works.

Sep 13 '06 #2

treschau...@yahoo.com wrote:
This is for SQL 2000. Running msdb..sp_help_job will display the
information. I dug through the msdb..sp_help_jobschedule and put the
following query together:

select name
,cast(left(cast(next_run_date as varchar),4)+'-'+
substring(cast(next_run_date as
varchar),5,2)+'-'+right(cast(next_run_date as varchar),2) +' '+
left(cast(next_run_time as varchar),2)+':'+substring(cast(next_run_time
as varchar),3,2)+':'+right(cast(next_run_time as varchar),2) as
datetime) "Next_Run"
from msdb..sysjobschedules

Converting the next_run_date and next_run_time columns got kind of
messy, but it works.
This is not as good I hope. If the job isn't going to run until
tomorrow the next_run_date and next_run_time columns are zero.

Sep 13 '06 #3
I thought you had it there :) Its a doozy I guesss

thanks for trying anyway

Bryan
tr*********@yahoo.com wrote:
treschau...@yahoo.com wrote:
This is for SQL 2000. Running msdb..sp_help_job will display the
information. I dug through the msdb..sp_help_jobschedule and put the
following query together:

select name
,cast(left(cast(next_run_date as varchar),4)+'-'+
substring(cast(next_run_date as
varchar),5,2)+'-'+right(cast(next_run_date as varchar),2) +' '+
left(cast(next_run_time as varchar),2)+':'+substring(cast(next_run_time
as varchar),3,2)+':'+right(cast(next_run_time as varchar),2) as
datetime) "Next_Run"
from msdb..sysjobschedules

Converting the next_run_date and next_run_time columns got kind of
messy, but it works.

This is not as good I hope. If the job isn't going to run until
tomorrow the next_run_date and next_run_time columns are zero.
Sep 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by A. Lovhaug | last post: by
5 posts views Thread by Shabam | last post: by
6 posts views Thread by John Bowman | last post: by
1 post views Thread by steve | last post: by
1 post views Thread by satelite | last post: by
1 post views Thread by TC | last post: by
reply views Thread by =?Utf-8?B?TWVlbWEgSnVkeQ==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.