Friday, January 16

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

10 comments:

Peter said...

Hey Scott, our DBAs also hated dealing with awkward RS job GUIDs. Our software SQL Sentry Event Manager maps the GUIDs to friendly report names and can link performance counters to any Reporting Services task. Check www.sqlsentry.net for more info.

HGHumphrey said...

I get the following error:

Msg 536, Level 16, State 5, Line 3
Invalid length parameter passed to the SUBSTRING function.

Scott Herbert said...

Thanks for the heads up HGHumphrey; I got the same error. It should be working now.

Vítor said...

Hey Scott,

How did you solve the problem "Only members of sysadmin role are allowed to update or delete jobs owned by a different login." when you start to play with the SQL Agent jobs names? I'm stuck there :)

Thanks!

Scott Herbert said...

I changed the owners of the jobs!

Try something along the lines of

USE ReportServer

SELECT * FROM dbo.Users

UPDATE dbo.Catalog SET CreatedByID = 'Insert GUID here'

William Hug said...

You forgot the best part! Restarting SSRS subscriptions without SSRS.

exec msdb.dbo.sp_start_job @job_name='guid here'

James Traub said...

Dude thank for the SQL!

Wojciech Gaweł said...

I was looking for smth like that for some time. Tahnks you man!!

sai pallavi said...

Useful post for jobs like php openings in hyderabad

Santosh Kumar said...

I really appreciate your Post. the post has excellent tips which are useful. this post is good in regards of both knowledge as well as information SQL Jobs