Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Get job list
Add to briefcase
11 posts, Page 1 of 2
12»»

Get job list Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
balasach82
Posted Tuesday, October 30, 2012 4:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743
Need a query which gets me the list of jobs which is scheduled to run on a particular day and its scheduled run time. I need the schedule even if the job is disabled, but schedule enabled.

I dont want to create a function. A query without creation of a stored procedure or function would be helpful.
Post #1378649
derek.colley
Posted Tuesday, October 30, 2012 4:23 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:19 AM
Points: 496, Visits: 584
In the simplest form, with no filters:

SELECT	sj.[name],
		sjs.next_run_date, sjs.next_run_time
FROM	        MSDB..sysjobs sj
JOIN	        MSDB..sysjobschedules sjs ON sj.job_id = sjs.job_id

You can use MSDB..sysjobs, MSDB..sysjobschedules, MSDB..sysjobhistory, MSDB..sysjobservers, MSDB..sysjobactivity to JOIN and filter your data using WHERE clauses, and order the data.

You can use the 'enabled' column in sysschedules too if you want to filter on schedule enabled and ignore the 'enabled' column in sysjobs.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1378662
balasach82
Posted Tuesday, October 30, 2012 4:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743
Query looks great. But, it missed out one detail. If the job is scheduled to run daily, then it gives the next run only and does not give the day after tomorrow's run date/time.

If job runs daily, and i run the query on wednesday, it gives thursday date/time, but not fri,sat,sun dates. If i want to see what jobs run on sunday, how to include that in this (or any other) query.
Post #1378667
derek.colley
Posted Tuesday, October 30, 2012 4:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:19 AM
Points: 496, Visits: 584
Ah, I see. msdb..sysschedules will be your friend then, it defines freq_interval, freq_type, etc. and you should be able to join on schedule_id with msdb..sysjobschedules as a filter.

Unfortunately I don't have time to put together the full query for you, but it's an interesting thing to do that I'll try and get around to - always nice to have something new for my code dump every day!

I would build this as an SP though - you can then feed in a date or range to get filtered info out.

If you wanted a quick view on jobs and schedules that you can eyeball, have you considered using Red Gate Backup to view and order the jobs?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1378675
balasach82
Posted Tuesday, October 30, 2012 4:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743
Oh oh... then i would be waiting for the script with bated breath :-(
Post #1378684
balasach82
Posted Tuesday, October 30, 2012 6:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743
In the meantime anyone out there who can help with the qury
Post #1378717
durai nagarajan
Posted Tuesday, October 30, 2012 7:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 856, Visits: 2,115
balasach82 (10/30/2012)
In the meantime anyone out there who can help with the qury


deifinetely but why dont you give a try and post what exactly are you expecting in terms of column details..

Regards
Durai Nagarajan
Post #1378763
durai nagarajan
Posted Tuesday, October 30, 2012 7:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 856, Visits: 2,115
check if this helps

exec msdb.dbo.sp_help_job

Regards
Durai Nagarajan
Post #1378771
derek.colley
Posted Tuesday, October 30, 2012 9:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:19 AM
Points: 496, Visits: 584
PMed you with rough but tested script.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1378843
durai nagarajan
Posted Wednesday, October 31, 2012 7:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 856, Visits: 2,115
derek.colley (10/30/2012)
PMed you with rough but tested script.


why not put it on forum for others to see.

Regards
Durai Nagarajan
Post #1379297
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.