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 » Article Discussions » Article Discussions by Author » Discuss content posted by Michael Abair » SQL Server Agent Schedule Decoder
Add to briefcase
21 posts, Page 2 of 3
««123»»

SQL Server Agent Schedule Decoder Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GabyYYZ
Posted Wednesday, January 06, 2010 9:20 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 @ 1:00 PM
Points: 802, Visits: 2,013
tskelley (1/6/2010)
Thanks, great script. And yes, SQL 2005 or higher. I am one of those dinosaurs that still defaults to SQL2000 to keep customer backward compatibility.

As for the formatting, I also paste into Notepad++, then just do a Replace All on four question marks (????) with four spaces and that does the trick. Something to do with the HTML code control. The interesting thing is pasting into MS Word handles the question marks, but includes the HTML control in the document, so you still have to copy the code out of the control to make it more readable.


For SQL 2000, try this script, been kicking around awhile, from mssqltips.com

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #842927
randal.schmidt
Posted Wednesday, January 06, 2010 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116
How would I do this since there is already a JOIN to sysschedules? Sorry, I am not a system DBA.
Post #842954
abair34
Posted Wednesday, January 06, 2010 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 6:01 PM
Points: 30, Visits: 250
Try This...
IT is written ASSUMING your reporting services database is named REPORTSERVER
and its COLLATION for the name column in the catalog table is Latin1_General_CI_AS_KS_WS

Let me know if it works for you.
-Michael Abair

  Post Attachments 
reportingservices_decoder.txt (12 views, 9.48 KB)
Post #843010
randal.schmidt
Posted Wednesday, January 06, 2010 11:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116
Perfect! Thanks for a very cool solution and the mods to make it workable for me.
Post #843046
Rudy Panigas
Posted Wednesday, January 06, 2010 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:35 AM
Points: 306, Visits: 1,020
Excellent script! Keep up the great work!

Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.

This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.

Rudy


Post #843158
gregory.anderson
Posted Thursday, January 07, 2010 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:44 AM
Points: 263, Visits: 434
Kinda weird, I do not have the table msdb.dbo.sysschedules...I can see sysjobschedules though.

Did I not install SQL Server [Agent] correctly?
Post #843506
gregory.anderson
Posted Thursday, January 07, 2010 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:44 AM
Points: 263, Visits: 434
Rudy Panigas (1/6/2010)
Excellent script! Keep up the great work!

Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.

This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.

Rudy


How about this script:
select\tj.job_id,       Replace(j.name, '''', '') As [name],\t\tConvert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')) AS [run_start],       CASE len(jh.run_duration)   WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8))  WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))  WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))    WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))  END as [run_duration],\t\tDateAdd(second, jh.run_duration, Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' 
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'))) AS [run_end],\t\tjh.message,\t\tjh.run_status   from msdb..sysjobs j (nolock)   inner join msdb..sysjobhistory jh (nolock) on jh.job_id = j.job_id   where jh.step_id = 0   order by j.name, jh.run_date desc
Post #843513
gregory.anderson
Posted Thursday, January 07, 2010 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:44 AM
Points: 263, Visits: 434
Rudy Panigas (1/6/2010)
Excellent script! Keep up the great work!

Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.

This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.

Rudy


Wow, couldn't read my first reply, try this one:

select  j.job_id,       
Replace(j.name, '''', '') As [name],    
Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')) AS [run_start],       
CASE len(jh.run_duration)   
WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8)) 
WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8))  
WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8)) 
WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))  
WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))    
WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))  
END as [run_duration],    
DateAdd(second, jh.run_duration, Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'))) AS [run_end],    
jh.message,    
jh.run_status   
from msdb..sysjobs j (nolock)   
inner join msdb..sysjobhistory jh (nolock) on jh.job_id = j.job_id   
where jh.step_id = 0   
order by j.name, jh.run_date desc
Post #843517
Rudy Panigas
Posted Thursday, January 07, 2010 7:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:35 AM
Points: 306, Visits: 1,020
Wow. Works nice. When I have some time, I'll see about combining the 2 scripts so that you get a report that displays the combined data. This would make an excellent SSRS report for DBAs.

Very nicely done!

Rudy


Post #843523
Michael-321617
Posted Thursday, July 22, 2010 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 14, 2011 1:32 PM
Points: 3, Visits: 55
This is great....Thanks for the script
Post #957220
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

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