How can i link a sql server job name

Question

to the SSRS report server process that uses it? the name looks like a guid, but i need to find the reporting services reports that use it. thanks very much

Answer 1

There doesn't appear to be a straightforward method to find this out.

The following query lists the subscription IDs and the reports to which they link

select s.SubscriptionID, c.Path
from ReportServer.dbo.Subscriptions as s
JOIN ReportServer.dbo.Catalog       as c
on   ItemID = Report_OID

The subscription ID is then referenced inside the job step, in the following format.

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='~subscriptionID~'

It should be possible to write a query to join it all together, but I don't have time right now. I will try and update the question later.

Answer 2

Here's a query I blogged about a while back that does the join:

;WITH cte (job_id, job_name, execution_time, execution_order) 
AS 
( 
SELECT DISTINCT j.job_id 
    ,j.name 
    ,CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/') 
        + SPACE(1) 
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':'))
    ,ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/') 
        + SPACE(1) 
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':')) DESC)
FROM msdb.dbo.sysjobs j  
    INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id 
WHERE c.name ='Report Server'
) 
SELECT 
    x.job_name 
    ,c.name 
    ,x.execution_time
    ,c.path 
    ,su.description 
    ,CONVERT(varchar(max), su.ExtensionSettings) as ExtensionSettings 
    ,'EXEC msdb..sp_start_job ''' + x.job_name + '''' as SQLStatement
FROM cte x 
    INNER JOIN dbo.Schedule sc ON x.job_name = CONVERT(varchar(100), sc.ScheduleID) 
    INNER JOIN dbo.ReportSchedule rs ON sc.ScheduleID = rs.ScheduleID 
    INNER JOIN dbo.Subscriptions su ON rs.SubscriptionID = su.SubscriptionID 
    INNER JOIN dbo.Catalog c ON su.Report_OID = c.ItemID 
WHERE execution_order = 1
ORDER BY 3, 2