Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server Agent Schedule Decoder Expand / Collapse
Author
Message
Posted Wednesday, January 6, 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 @ 12:59 PM
Points: 810, Visits: 2,119
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
Posted Wednesday, January 6, 2010 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
How would I do this since there is already a JOIN to sysschedules? Sorry, I am not a system DBA.
Post #842954
Posted Wednesday, January 6, 2010 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:48 PM
Points: 30, Visits: 256
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 (13 views, 9.48 KB)
Post #843010
Posted Wednesday, January 6, 2010 11:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
Perfect! Thanks for a very cool solution and the mods to make it workable for me.
Post #843046
Posted Wednesday, January 6, 2010 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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
Posted Thursday, January 7, 2010 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
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
Posted Thursday, January 7, 2010 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
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
Posted Thursday, January 7, 2010 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
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
Posted Thursday, January 7, 2010 7:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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
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 ««123»»

Permissions Expand / Collapse