CODEWITHSUNDEEP
reporting-services
Mike
Mike

Reputation: 51

Is there a way to find the Enabled/Disabled status of a SSRS subscription?

I have been tasked to write a report that allows the entry of an email address and report all report names and its active status (Disabled or Expired) where the email address is found. I found the following query on stackoverflow, but I cannot see a column that would indicate whether or not the subscription is Disabled or Expired.

I found 1 column "enabled", but doing a SELECT DISTINCT(enabled) only produced a single return of "1".

Any suggestions on what table I need to include to be able to include the subscription status in my output?

use ReportServer

SELECT  top 10 *
FROM 
dbo.[Catalog] c
RIGHT OUTER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
RIGHT OUTER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
RIGHT OUTER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
RIGHT OUTER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
where coalesce(c.Name,' ') != ' '
order by c.Name

Upvotes: 2

Views: 2853

Answers (1)

Mike
Mike

Reputation: 51

Found it.

Enabled:  dbo.[Subscriptions].InactiveFlags = 0
Disabled:  dbo.[Subscriptions].InactiveFlags = 128

Upvotes: 3

Related Questions