SQL script to show you a list of your SSRS subscriptions

At work we use SQL Server Reporting Services (SSRS) for our reporting needs. I like to have a few SQL Scripts to help me keep track of our subscriptions, when they ran and check on any errors.

Here’s a couple of SQL Scripts that I use when needed and I don’t have the website where I found these but if you know where they we’re originally posted, let me know so I can give credit where credit is due.

LIST OF SSRS SUBSCRIPTIONS

SELECT SubscriptionID ,
d.LastStatus ,
e.Name ,
e.Path ,
d.DeliveryExtension ,
d.LastRunTime ,
d.ExtensionSettings
FROM Subscriptions d
JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID

LIST OF SSRS SUBSCRIPTIONS ORDER BY MOST RECENTLY RAN REPORT

SELECT SubscriptionID ,
d.LastStatus ,
e.Name ,
e.Path ,
d.DeliveryExtension ,
d.LastRunTime ,
d.ExtensionSettings
FROM Subscriptions d
JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
ORDER BY d.LastRunTime DESC;

LIST OF SSRS SUBSCRIPTIONS SETUP FOR FILESHARE DELIVERY

SELECT  SubscriptionID ,
        d.LastStatus ,
        e.Name ,
        e.Path ,
        d.DeliveryExtension ,
        d.LastRunTime ,
        d.ExtensionSettings
FROM    Subscriptions d
        JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
WHERE DeliveryExtension = 'Report Server FileShare'

LIST OF SSRS SUBSCRIPTIONS SETUP FOR EMAIL DELIVERY

SELECT  SubscriptionID ,
        d.LastStatus ,
        e.Name ,
        e.Path ,
        d.DeliveryExtension ,
        d.LastRunTime ,
        d.ExtensionSettings
FROM    Subscriptions d
        JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
WHERE DeliveryExtension = 'Report Server Email'

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.