SSRS Subscription Schedules

, 2017-06-06 (first published: )

Reporting Services

Reporting Services (SSRS) is a tool that permits you to create and deliver feature rich reports. The reports can be delivered in various formats and can even be scheduled to be delivered at various times. I even recently wrote about creating more advanced custom schedules.

With the ability to create content delivery schedules, or to subscribe to report content delivery, this imposes a requirement to also know when the various reports are scheduled to be delivered.

SSRS provides the means to be able to review the scheduled reports (subscriptions). That means is held within the ReportServer database. This article will help to uncover one of the sources of this scheduling information within the ReportServer database.

Review Schedules

When looking into the database for SSRS, I can see there are different means to be able to review the report schedules. This article is going to cover just one of those methods. And if I am going to be entirely up front about this method, I don’t like it and I recommend that it not be use.

I can hear the moans now. “If you don’t like it, then why show it to us?” Well, that is a very good question and there is a very good reason for this decision. A lesson I learned a long time ago is sometimes you need to learn the hard way, or less desirable way, to do various things. One of my favorite Calculus teachers from years ago drilled this into my head over and over again. Why? Well, there are three good reasons that come to mind: a) it makes the more desirable method seem much easier, b) it helps you to appreciate the more desirable method all that much more, and c) because if all else fails, you will have another method to fall back to just in case.

Less Disérables

The least desirable method (at least of the methods I will share) is to parse XML from a field stored in the ReportServer database. If I look into the Schedule table within the RepotServer database, I will find this column called MatchData. Up front, this field is not very intuitively named. I would not think this field actually represented the schedule, but it actually does.

Before we start diving into parsing XML, we need an example of what this XML may look like. The following will provide that very example that we need.

<ScheduleDefinition xmlns:xsd="" xmlns:xsi="">
  <StartDateTime xmlns="">2017-03-27T02:00:00.000-06:00</StartDateTime>
  <MonthlyRecurrence xmlns="">
	<MinuteRecurrence xmlns="">
	<WeeklyRecurrence xmlns="">

Are your gears grinding yet? The XML is not terribly difficult to follow. I am sure you have realized the problem from this format at this point. If I query this to make it human readable in a tabular format (you know DBA format), I will end up with a really wide table that is pretty ugly to look at (unless I get super creative to combine fields etc).

Let’s take a look at the query to parse something like the preceding XML example.

, getxml AS (
	SELECT SUB.SubscriptionID
			, ReportName = CAT.[Name]
			, ExtensionSettings = CAST(SUB.ExtensionSettings AS XML)
			, ReportSchedule = CAST(SUB.MatchData AS XML)
		FROM dbo.Subscriptions AS SUB
			INNER JOIN dbo.Users AS USR
				ON SUB.OwnerID = USR.UserID
			INNER JOIN dbo.[Catalog] AS CAT
				ON SUB.Report_OID = CAT.ItemID
			INNER JOIN dbo.ReportSchedule AS RS
				ON SUB.Report_OID = RS.ReportID
					AND SUB.SubscriptionID = RS.SubscriptionID
			INNER JOIN dbo.Schedule AS SCH
				ON RS.ScheduleID = SCH.ScheduleID 
  --WHERE CONVERT(CHAR(8), SUB.LastRunTime, 112  ) > '20170101' 
SELECT SubscriptionID
		, ReportName
		, [StartHour] = SUBSTRING(( c.nd.value('(rs:StartDateTime/text())[1]',
												'VARCHAR(500)') ), 12, 2)
		, [StartMin] = SUBSTRING(( c.nd.value('(rs:StartDateTime/text())[1]',
												'VARCHAR(500)') ), 15, 2)
		, [Days] = c.nd.value('(rs:MonthlyRecurrence/rs:Days/text())[1]', 'INT')
		, [January] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:January/text())[1]',
		, [February] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:February/text())[1]',
		, [March] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:March/text())[1]',
		, [April] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:April/text())[1]',
		, [May] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:May/text())[1]',
		, [June] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:June/text())[1]',
		, [July] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:July/text())[1]',
		, [August] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:August/text())[1]',
		, [September] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:September/text())[1]',
		, [October] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:October/text())[1]',
		, [November] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:November/text())[1]',
		, [December] = c.nd.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:December/text())[1]',
		, [Sunday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Sunday/text())[1]',
		, [Monday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Monday/text())[1]',
		, [Tuesday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Tuesday/text())[1]',
		, [Wednesday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Wednesday/text())[1]',
		, [Thursday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Thursday/text())[1]',
		, [Friday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Friday/text())[1]',
		, [Saturday] = c.nd.value('(rs:WeeklyRecurrence/rs:DaysOfWeek/rs:Saturday/text())[1]',
		, [WeeksInterval] = c.nd.value('(rs:WeeklyRecurrence/rs:WeeksInterval/text())[1]',
		, [MinuteRecurrence] = c.nd.value('(rs:MinuteRecurrence/rs:MinutesInterval/text())[1]',
	FROM getxml
		CROSS APPLY ReportSchedule.nodes('//ScheduleDefinition') c ( nd )
	ORDER BY SubscriptionID
		, ReportName;

And there we have that ugly query to produce a really wide ugly result set. The query is not difficult to write. It’s just extremely repetitive. In similar fashion, the results are very repetitive. This makes, in my eyes, this particular method less desirable.

I haven’t even gotten to the part about the shortcoming in scheduling reports through SSRS that I wrote about recently – here. In that article I discussed a workaround to overcome the SSRS scheduling options. If you employ methods such as I discussed there, then this query will never fully cover the scheduling related to your reports. Because of that, I will be discussing the better solution in the next article.


SSRS provides built-in mechanisms to help report on the scheduled reports that have been deployed. While parsing the XML is less desirable than what I will be sharing in the near future, it is better than doing nothing at all. I recommend you start looking into the various report schedules you may already have in your environment. Also, stay tuned for the next article that will better show these schedules.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads