1

I would like to retrieve SSRS data-driven subscription detail from the ReportServer DB. I am able to retrieve most of the detail from the table Subscriptions but I need the dataset information (SQL Query itself) which is used for Data-driven subscriptions.

Can I achieve that?

alex
  • 51
  • 4
  • 10

1 Answers1

0

I was wondering the same thing recently and did some research + guessing. It's a complicated query but I believe this is what you're looking for. It will only show reports that ran in the last 30 days so you'll want to adjust it if you want everything.

The key here is the Subscriptions.DataSettings which is the field that contains the query you'll be looking for.

SELECT s.SubscriptionID, c.Name
, c.Type
,s.EventType
, c.Description
, u.UserName AS CreatedBy
, c.CreationDate
, c.ModifiedDate
, s.Description AS Subscription
, s.DeliveryExtension AS SubscriptionDelivery
, d.Name AS DataSource
, s.LastStatus
, s.LastRunTime
, s.Parameters
, sch.StartDate AS ScheduleStarted
, sch.LastRunTime AS LastSubRun
, sch.NextRunTime
, c.Path
,s.DataSettings
FROM Catalog c
INNER JOIN
Subscriptions s ON c.ItemID = s.Report_OID
INNER JOIN
DataSource d ON c.ItemID = d.ItemID
LEFT OUTER JOIN
Users u ON u.UserID = c.CreatedByID
LEFT OUTER JOIN
ReportSchedule rs ON c.ItemID = rs.ReportID
LEFT OUTER JOIN
Schedule sch ON rs.ScheduleID = sch.ScheduleID
WHERE (c.Type = 2) 
and s.DataSettings is not null
and s.LastRunTime > getdate()-31
ORDER BY c.Name
mitzman
  • 61
  • 3