SQLServerPedia

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