Warning: Don’t play with Reporting Services’ SQL Agent Jobs! Or How to Tell Which Job is Doing What

Having a look through SQL Server Agent’s list of jobs yesterday, I got a bit upset. About half the jobs in there are named things like “0343229B-0642-4E38-B7A5-C603C1F45976”. They’re Reporting Services Subscription jobs. Once again RS looks like a half-arsed product.

So I decide to go about renaming them, figuring that RS’ subscriptions will still be able to recognize the jobs as I’m only changing their names, not their IDs. Bad move. RS uses the names, can’t find the jobs when you restart the service, and recreates all those jobs with new GUIDs. Worse yet, I started getting “Only members of sysadmin role are allowed to update or delete jobs owned by a different login” errors whenever I tried to update subscriptions through the Report Manager, forcing me to have to play around with login permissions and job owners. A nightmare!

The script I wrote to help me recognize which job fires which subscription is below. It’s now more useful than ever:

NB: The CASE statement which transforms the “DaysOfWeek” int figure into actual days of the week doesn’t cover ever possible case, but it covered my needs. For a rundown of how this int works, see “Toolman’s” post at http://www.sqlservercentral.com/Forums/Topic501408-150-1.aspx

Also thanks to “stevefromOZ” from whose post at http://www.sqlservercentral.com/Forums/Topic254010-150-1.aspx I nabbed the email address part of the code below.

USE ReportServer
SELECT
         sysjobs.name,
   'RS - '
         + Catalog.Name
         + ' ['
         + CASE
            WHEN DaysOfMonth IS NOT NULL
            THEN CAST(DaysOfMonth AS VARCHAR(10)) + ' Day of Month'
            WHEN DaysOfWeek = 1 THEN 'Monday'
            WHEN DaysOfWeek = 2 THEN 'Tuesday'
            WHEN DaysOfWeek = 4 THEN 'Wednesday'
            WHEN DaysOfWeek = 8 THEN 'Thursday'
            WHEN DaysOfWeek = 16 THEN 'Friday'
            WHEN DaysOfWeek = 32 THEN 'Saturday'
            WHEN DaysOfWeek = 64 THEN 'Sunday'
            WHEN DaysOfWeek = 62 THEN 'Monday - Friday'
            WHEN DaysOfWeek = 120 THEN 'Wednesday - Saturday'
            WHEN DaysOfWeek = 126 THEN 'Monday - Saturday'
            WHEN DaysOfWeek = 127 THEN 'Daily'
   END
         + ' '
         + CAST(DATEPART(hh,Schedule.StartDate)AS VARCHAR(2))
         + CASE
            WHEN LEN(CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))) = 1
            THEN ':0' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   ELSE ':' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   END
         + ']' [NewName]
   FROM
         msdb.dbo.sysjobs
         JOIN
         dbo.ReportSchedule
         ON sysjobs.name = CAST(ReportSchedule.ScheduleID AS VARCHAR(255))
         JOIN
         dbo.Schedule
         ON ReportSchedule.ScheduleID = Schedule.ScheduleID
         JOIN
         dbo.Catalog
         ON ReportSchedule.ReportID = Catalog.ItemID
   ORDER BY
         Catalog.name

Querying ReportServer Database

Here's a script I knocked up which gives you some insights into your Reports Catalog in Reporting Services.

I got frustrated with the Reports Manager site and its inability to give you a wholistic view of subscriptions, and the script blew out a little from there as I had a scrounge around the ResportServer database structure.

I might hook this up to an RS report at some stage, if that's not akin to "crossing the streams".

Note that info about the number of executions and last execution time are based on the execution log, which only keeps records for the last 60 days by default.

USE ReportServer
SELECT
         CatalogParent.Name ParentName,
         Catalog.Name ReportName,
         ReportCreatedByUsers.UserName ReportCreatedByUserName,
         Catalog.CreationDate ReportCreationDate,
         ReportModifiedByUsers.UserName ReportModifiedByUserName,
         Catalog.ModifiedDate ReportModifiedDate,
         CountExecution.CountStart TotalExecutions,
         ExecutionLog.InstanceName LastExecutedInstanceName,
         ExecutionLog.UserName LastExecutedUserName,
         ExecutionLog.Format LastExecutedFormat,
         ExecutionLog.TimeStart LastExecutedTimeStart,
         ExecutionLog.TimeEnd LastExecutedTimeEnd,
         ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
         ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
         ExecutionLog.TimeRendering LastExecutedTimeRendering,
         ExecutionLog.Status LastExecutedStatus,
         ExecutionLog.ByteCount LastExecutedByteCount,
         ExecutionLog.[RowCount] LastExecutedRowCount,
         SubscriptionOwner.UserName SubscriptionOwnerUserName,
         SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,
         Subscriptions.ModifiedDate SubscriptionModifiedDate,
         Subscriptions.Description SubscriptionDescription,
         Subscriptions.LastStatus SubscriptionLastStatus,
         Subscriptions.LastRunTime SubscriptionLastRunTime
   FROM
         dbo.Catalog
         JOIN
         dbo.Catalog CatalogParent
         ON Catalog.ParentID = CatalogParent.ItemID
         JOIN
         dbo.Users ReportCreatedByUsers
         ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
         JOIN
         dbo.Users ReportModifiedByUsers
         ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
         LEFT JOIN
   (
   SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) LatestExecution
         ON Catalog.ItemID = LatestExecution.ReportID
         LEFT JOIN
   (
   SELECT
            ReportID,
            COUNT(TimeStart) CountStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) CountExecution
         ON Catalog.ItemID = CountExecution.ReportID
         LEFT JOIN
         dbo.ExecutionLog
         ON LatestExecution.ReportID = ExecutionLog.ReportID
      AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
         LEFT JOIN
         dbo.Subscriptions
         ON Catalog.ItemID = Subscriptions.Report_OID
         LEFT JOIN
         dbo.Users SubscriptionOwner
         ON Subscriptions.OwnerID = SubscriptionOwner.UserID
         LEFT JOIN
         dbo.Users SubscriptionModifiedByUsers
         ON Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID
   ORDER BY
         CatalogParent.Name,
         Catalog.Name 

The Command Line

I didn't go to school to learn about computers, LIFE taught me about computers. I learned SQL on the job (well, maybe there's lots of study that's gone on since).

As a consequence, while I can write T-SQL better than I can write complete sentences in english, there's at least a few little basics that have escaped me along the line. So yesterday when I was trying to work out how to bring up a remote failed cluster node onto which I could no longer remote desktop, it was a revelation to me when someone told me how to remotely reboot a computer from the command line.

I thought "how do people know this black magic"?

And then I found the goods: An A-Z Index of the Windows XP command line

which looks quite similar to the TechNet article, but I'm not complaining.