[SSRS] Finding SSRS subscription job details

Below, we will generate the T-SQL that is used inside the SQL Agent job directly from the tables and then just run the SQL.
It’s a pure SQL based solution.

After you run this query, the [ReportCommand] column that has the EXEC command
for the report you want to run and paste the EXEC command into a query window to execute the code to re-run the report.

NOTE:
It’s possible for a ReportSchedule record to exist, but not have a corresponding Subscription !

SELECT
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', '
'@EventData='''+CAST(sub.SubscriptionID AS VARCHAR(40))+'''' [ReportCommand]
, sysjob.name AS JobName
, sub.SubscriptionID
, cat.name
, cat.path
, sub.description
, sub.laststatus
, sub.eventtype
, sub.LastRunTime
, sysjob.date_created
, sysjob.date_modified
from ReportServer.dbo.Catalog cat
JOIN ReportServer.dbo.Subscriptions sub
 ON sub.report_oid = cat.ItemID
JOIN ReportServer.dbo.ReportSchedule sched
 ON sched.SubscriptionID = sub.SubscriptionID
JOIN msdb.dbo.sysjobs sysjob
 ON CAST(sched.ScheduleID AS VARCHAR(40)) = sysjob.name
--WHERE cat.[path] LIKE '%job%'
WHERE sub.LastRunTime >= '19-oct-2010'
ORDER BY sub.LastRunTime
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s