Questions about SQL Monitor report (SQL Agent Job Summary)

samalexsamalex Posts: 20
edited November 14, 2011 5:00AM in SQL Monitor Previous Versions
First thing, thanks to whoever created the SSRS reports for SQL Monitor at Red Gate... these are exactly what I needed, especially the SQL Agent Job Summary report. I do however have some questions regarding this report.

I'm on SQL Monitor 2.3 and on the report it's showing the dates in UTC instead of our local time zone which is CST. Is there a simple way to change this without modifying the report?

And second the Duration appears to be incorrect. For example I have one job that ran in 31 minutes 44 seconds, yet the duration for it in the report is 19.04. I'm not sure what this represents.

Also something to note is the SQL Server being monitored is SQL 2000, and I know MS SQL changed the way it stores duration between SQL 2000 and 2005 so this maybe the difference .. not sure.

So please advise on this because once these two fields are reporting correct I can definitely work this into my weekly reports.

Take care --

Sam Alexander
· Share on Twitter

Comments

  • priyasinhapriyasinha Posts: 548 Silver 1
    Hi Sam,

    Thanks a lot for your post and great to hear that you are liking SSRS report.

    Regarding time format, all the dates are stored in UTC in SQL Monitor repository. Not sure if you really want to change this to CST as then the report will show wrong time when clock change happens for day light saving. I have not used Reporting services a lot so not very sure but I think you would have to change the report directly.

    I verified the duration, apologies it is typo on our end. The error is happening because of two extra zeros in conversion. So 19.04 is actually 1904 seconds which is 31 minutes and 44 seconds. We will upload the modified correct report on our website on Monday. But for now, I am emailing you the rdl file for your use. Thanks for pointing this out.

    Regards,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
    · Share on Twitter
  • samalexsamalex Posts: 20
    Priya,

    As always thanks for the quick response, and this will help tremendously.

    As for UTC vs CST I definitely see the reasons to save Datetime fields in UTC, but for reporting I need them in CST given all our other reports use CST. I'm actually working on a function now to convert UTC to any given timezone offset based on the DST standards, even taking into account the difference in standards before 2007. It's definitely some funky logic to work out, but once it's working I'll just use this in the report for the Datetime fields. I just thought maybe you guys had another simpler suggestion...

    Take care --

    Sam
    · Share on Twitter
  • priyasinhapriyasinha Posts: 548 Silver 1
    Thanks for your post Sam. Would it be possible for you to send us this report after you have worked it out? I am sure it will be useful for other SQL Monitor users also. You already have my personal email id so if you can email me that ... it will be great.

    Thanks,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
    · Share on Twitter
Sign In or Register to comment.