SSRS stores all of its data in the ReportServer database so you'll need an account with read access to this database. This is all relevant for SSRS running in native mode. I'm also not sure if shared schedules or data-driven subscriptions will be handled by this code, but I'm pretty sure they will be. I just haven't tested them.
PLEASE NOTE: Microsoft does not recommend or support directly querying the ReportServer database. They could change the structure in the next version or update of SSRS and you likely won't get any warning. The Microsoft recommendation is to always use the SSRS web service when you need to interrogate information about reporting services.
These are the tables that are relevant for pulling out the subscription information:
- dbo.Catalog - Information about the deployed reports
- dbo.ReportSchedule - Information relating reports to schedules and schedules
- dbo.Subscriptions - Information about the subscriptions
- dbo.Schedule - Information about the schedules
The SQL below pulls out schedule interval information for all reports. It doesn't calculate the next run dates but by figuring out the interval that the schedule is supposed to run on you can write another query to generate the actual dates.
This SQL was originally written for a report that just displays a string describing the interval so the final output is probably not what you're after. It should give you a good starting point though since it does figure out all of the interval details.
--these CTEs are used to match the bitmask fields in the schedule to determine which days & months the schedule is triggered on
WITH wkdays AS (
SELECT 'Sunday' AS label, 1 AS daybit
UNION ALL
SELECT 'Monday', 2
UNION ALL
SELECT 'Tuesday', 4
UNION ALL
SELECT 'Wednesday', 8
UNION ALL
SELECT 'Thursday', 16
UNION ALL
SELECT 'Friday', 32
UNION ALL
SELECT 'Saturday', 64
),
monthdays AS (
SELECT CAST(number AS VARCHAR(2)) AS label,
POWER(CAST(2 AS BIGINT),number-1) AS daybit
FROM master.dbo.spt_values
WHERE type='P' AND number BETWEEN 1 AND 31
),
months AS (
SELECT DATENAME(MM,DATEADD(MM,number-1,0)) AS label,
POWER(CAST(2 AS BIGINT),number-1) AS mnthbit
FROM master.dbo.spt_values
WHERE type='P' AND number BETWEEN 1 AND 12
)
SELECT cat.path,
cat.name,
cat.creationdate,
cat.modifieddate,
subs.Description,
subs.LastStatus,
subs.LastRunTime,
subs.InactiveFlags,
CASE RecurrenceType
WHEN 1 THEN 'Once'
WHEN 2 THEN 'Hourly'
WHEN 3 THEN 'Daily' --by interval
WHEN 4 THEN
CASE
WHEN WeeksInterval>1 THEN 'Weekly'
ELSE 'Daily' --by day of week
END
WHEN 5 THEN 'Monthly' --by calendar day
WHEN 6 THEN 'Monthly' --by day of week
END AS sched_type,
sched.StartDate,
sched.MinutesInterval,
sched.RecurrenceType,
sched.DaysInterval,
sched.WeeksInterval,
sched.MonthlyWeek,
wkdays.label AS wkday,wkdays.daybit AS wkdaybit,
monthdays.label AS mnthday,monthdays.daybit AS mnthdaybit,
months.label AS mnth, months.mnthbit
INTO #t
FROM dbo.Catalog AS cat
LEFT JOIN dbo.ReportSchedule AS repsched ON repsched.ReportID=cat.ItemID
LEFT JOIN dbo.Subscriptions AS subs ON subs.SubscriptionID=repsched.SubscriptionID
LEFT JOIN dbo.Schedule AS sched ON sched.ScheduleID=repsched.ScheduleID
LEFT JOIN wkdays ON wkdays.daybit & sched.DaysOfWeek > 0
LEFT JOIN monthdays ON monthdays.daybit & sched.DaysOfMonth > 0
LEFT JOIN months ON months.mnthbit & sched.[Month] > 0
WHERE cat.ParentID IS NOT NULL --all reports have a ParentID
/* THE PREVIOUS QUERY LEAVES MULTIPLE ROWS FOR SUBSCRIPTIONS THAT HAVE MULTIPLE BITMASK MATCHES *
* THIS QUERY WILL CONCAT ALL OF THOSE FIELDS TOGETHER AND ACCUMULATE THEM IN A TABLE FOR USE LATER. */
CREATE TABLE #c (type VARCHAR(16) COLLATE Latin1_General_CI_AS_KS_WS, name VARCHAR(255) COLLATE Latin1_General_CI_AS_KS_WS, path VARCHAR(255) COLLATE Latin1_General_CI_AS_KS_WS, concatStr VARCHAR(2000) COLLATE Latin1_General_CI_AS_KS_WS);
WITH d AS (
SELECT DISTINCT path,
name,
mnthday AS lbl,
mnthdaybit AS bm
FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'monthday' AS type,
t1.path,t1.name,
STUFF((
SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
FROM d AS t2
WHERE t2.path=t1.path AND t2.name=t1.name
ORDER BY bm
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;
WITH d AS (
SELECT DISTINCT path,
name,
wkday AS lbl,
wkdaybit AS bm
FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'weekday' AS type,
t1.path,t1.name,
STUFF((
SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
FROM d AS t2
WHERE t2.path=t1.path AND t2.name=t1.name
ORDER BY bm
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;
WITH d AS (
SELECT DISTINCT path,
name,
mnth AS lbl,
mnthbit AS bm
FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'month' AS type,
t1.path,t1.name,
STUFF((
SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
FROM d AS t2
WHERE t2.path=t1.path AND t2.name=t1.name
ORDER BY bm
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;
/* PUT EVERYTHING TOGETHER FOR THE REPORT */
SELECT a.path,a.name,a.sched_type,
a.creationdate,a.modifieddate,
a.description AS sched_desc,
a.laststatus AS sched_laststatus,
a.lastruntime AS sched_lastrun,
a.inactiveflags AS sched_inactive,
CASE RecurrenceType
WHEN 1 THEN 'Run once on '
ELSE 'Starting on '
END + CAST(StartDate AS VARCHAR(32)) + ' ' +
CASE RecurrenceType
WHEN 1 THEN ''
WHEN 2 THEN 'repeat every ' + CAST(MinutesInterval AS VARCHAR(255)) + ' minutes.'
WHEN 3 THEN 'repeat every ' + CAST(DaysInterval AS VARCHAR(255)) + ' days.'
WHEN 4 THEN
CASE
WHEN WeeksInterval>1 THEN 'repeat every ' + CAST(WeeksInterval AS VARCHAR(255)) + ' on ' + COALESCE(wkdays.concatStr,'')
ELSE 'repeat every ' + COALESCE(wkdays.concatStr,'')
END
WHEN 5 THEN 'repeat every ' + COALESCE(mnths.concatStr,'') + ' on calendar day(s) ' + COALESCE(mnthdays.concatStr,'')
WHEN 6 THEN 'run on the ' + CASE MonthlyWeek WHEN 1 THEN '1st' WHEN 2 THEN '2nd' WHEN 3 THEN '3rd' WHEN 4 THEN '4th' WHEN 5 THEN 'Last' END + ' week of ' + COALESCE(mnths.concatStr,'') + ' on ' + COALESCE(wkdays.concatStr,'')
END AS sched_pattern
FROM (
SELECT DISTINCT path,name,creationdate,modifieddate,description,laststatus,lastruntime,inactiveflags,sched_type,recurrencetype,startdate,minutesinterval,daysinterval,weeksinterval,monthlyweek
FROM #t
) AS a
LEFT JOIN #c AS mnthdays ON mnthdays.path=a.path AND mnthdays.name=a.name AND mnthdays.type='monthday'
LEFT JOIN #c AS wkdays ON wkdays.path=a.path AND wkdays.name=a.name AND wkdays.type='weekday'
LEFT JOIN #c AS mnths ON mnths.path=a.path AND mnths.name=a.name AND mnths.type='month'
DROP TABLE #t,#c;
Best Answer
The Report subscriptions are created as jobs for the SQL Agent. You should see a long list of jobs with GUIDs as the names. Make sure that the SQL agent jobs are running. Going through the Steps of the SQL Reporting Services Configuration tool will recreate these jobs if you were to move the Reporting Service installation to another server. (I don't recall which step recreates the subscriptions. Just go through each pane and click "Apply.")
Depending on how you move things, you may need to backup and restore encryption keys, and you can easily end up in a state where two copies of subscription exist and are executed ( on different servers.) But in your case, where you didn't move SSRS, I'd look at the SQL Agent properties on the original server (Express) to see that the SQL agent is enabled and running, and check the history of the SQL Agent jobs, to see if there are any problems.