Get the list of SSRS subscriptions

To get the list of all SSRS Subscriptions, just Log on to the Reporting Services instance and run the following query to retrieve the ID, Name, Path, Description ... of the jobs that launche the subscriptions :
Note that to use this query, it requieres to have permission on ReportServer database. especially SELECT permission.

SELECT b.NAME AS JobName, a.SubscriptionID, e.NAME, e.Path, d.Description, d.LastStatus, d.EventType, d.LastRunTime, b.date_created, b.date_modified FROM ReportServer.dbo.ReportSchedule AS INNER JOIN msdb.dbo.sysjobs AS ON CAST (a.ScheduleID AS SYSNAME) = b.NAME INNER JOIN ReportServer.dbo.ReportSchedule AS c ON b.NAME = CAST (c.ScheduleID AS SYSNAME) INNER JOIN ReportServer.dbo. Subscriptions AS d ON c.SubscriptionID = d.SubscriptionID INNER JOIN ReportServer.dbo.CATALOG AS e ON d.Report_OID = e.ItemID

You can also start a subscription with Transact SQL by obtaining the subscription ID with the above query, and run the following query:
Par exemple (ID dXCHARXabonnement: EFA0F359-B968-4FF4-95B6-8DE0C60A9008)

EXEC [ServerName].msdb.dbo.sp_start_job 'EFA0F359-B968-4FF4-95B6-8DE0C60A9008'

 

 

Leave a comment

Leave a Reply

Your email address Will not be published. Required fields are marked *