On the Eighth Day…

Today’s post is merely an illusion.  The illusion being that we have finally stopped talking about the msdb database.  I’ll explain about that later in this post.

This should be a good addition to the script toolbox for those Mere Mortal DBAs out there supporting their corporate SSRS environment.  Everybody could use a script now and then that helps them better support their environment and perform their DBA duties, right?

No reading ahead now.  We’ll get to the script soon enough.  First, we have a bit of business to cover just as we normally do.

We need to quickly recap the first seven days thus far (after all, the song does a recap with each day).

1. Runaway Jobs – 7th Day
2. Maintenance Plan Gravage – 6th Day
3. Table Compression – 5th Day
4. Exercise for msdb – 4th Day
5. Backup, Job and Mail History Cleanup – 3rd Day
6. Service Broker Out of Control – 2nd Day
7. Maint Plan Logs – 1st Day

On the Eighth Day of pre-Christmas…

My DBA gave to me a means to see Report Subscriptions and their schedules.

One of the intriguing points that we find with having a reporting environment is that we also need to report on that reporting environment.  And one of the nuisances of dealing with a Reporting Services Environment is that data like report subscription schedules is not very human friendly.

Part of the issue is that you need to be fluent with math.  Another part of the issue is that you need to be a little familiar with bitwise operations in SQL Server.  That said, it is possible to get by without understanding both very well.  And as a last resort, there is always the manual method of using Report Manager to check the subscriptions for each of the reports that have been deployed to that server.  Though, I think you will find this to be a bit tedious if you have a large number of reports.

I have seen more than one script that provides the schedule information for the subscriptions without using math and just relying on the bitwise operations.  This tends to produce a lot of repetitive code.  The method works, I’m just not that interested in the repetitive nature employed.

Within SQL Server you should notice that in several tables, views, and processes employ the powers of 2 or base 2 or binary number system.  This is natural since this is so integral within computer science in general.  Powers of 2 translates to binary fairly easily and then integrates so well with bitwise operations.

The following table demonstrates the powers of 2 and conversion to binary.

To get numbers and values between the binary results or the value results listed above is a matter of addition.  We add the value from a power of 2 to another power of 2.  So if I need a value of 7, then I need 2^0 + 2^1 + 2^2.  This results in a binary value of 0111.  Now this is where the need for bit comparisons comes into play so we will use some bitwise operations (read more here) to figure out quickly which values are used to reach an end value of 7 (so I don’t need to really know a lot of math there ).

How does this Apply to Schedules?

This background has everything to do with scheduling in SSRS.  Within the ReportServer database, there is a table called Schedule in the dbo schema.  This table has multiple columns that store pieces of the Subscription Schedule.  The three key columns are DaysofWeek, DaysofMonth and Month.  The values stored in these columns are all sums of the powers of 2 necessary to represent multiple days or months.

For instance, you may see the following values

These values are not friendly to normal every day mere mortal DBAs.  The values from the preceding table are shown below with the human friendly data they represent.

That is better for us to understand, but not necessarily better to store in the database.  So, I hope you can see the value of storing it in a numeric representation that is easily deciphered through math and TSQL.

Without further adieu, we have a script to report on these schedules without too much repetition.

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
WITH E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E2  UNION ALL SELECT 0
),powers(powerN,n) AS (SELECT POWER(2,N),N FROM cteTally WHERE N < 31)
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11 
THEN DateName(month,DATEADD(month,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers
SELECT DISTINCT s.ScheduleID,Ca.Path as ReportManagerPath,Ca.Name as ReportName
, U.UserName as SubscriptionCreator
,Su.Description as SubscriptionDescription,S.StartDate,Su.LastRunTime
,case 
when s.RecurrenceType = 1 then 'One Off'
when s.RecurrenceType = 2 then 'Hour'
when s.RecurrenceType = 4 then 'Daily'
when s.RecurrenceType = 5 then 'Monthly' 
when s.RecurrenceType = 6 then 'Week of Month' 
end as RecurrenceType
,s.EventType
,ISNULL(STUFF(
(Select ','+CONVERT(VARCHAR(50),MonthDate) AS [text()] 
FROM #morepower m1 
WHERE m1.powerN < s.DaysofMonth+1 
AND s.DaysofMonth & m1.powerN <>0 
ORDER BY N FOR XML PATH('')),1,1,''),'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN Catalog Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (Select s.ScheduleID,STUFF(
(SELECT ','+NameofMonth AS [text()] 
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL 
AND m1.powerN & s1.Month <>0 
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH('')),1,1,'') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,STUFF(
(SELECT ','+WkDay AS [text()] 
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL 
AND DaysOfWeek & m1.powerN <>0
AND  s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH('')),1,1,'') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
DROP TABLE #morepower;

Consider this as V1 of the script with expected changes coming forth.

I have set this up so a specific report name can be provided or not.  If not provided, the query will return all scheduling information for all reports.

Through the use of a numbers table (done through the CTE), I have been able to create a map table for each of the necessary values to be parsed from the schedule later in the script.  In the creation of that map table, note the use of the power function.  This map table was the critical piece in my opinion to create a script that could quickly decipher the values in the schedule and provide something readable to the DBA.

Conclusion

I did this script because I feel it important to know what reports are running and when they are running.  Add that management also likes to know that information, so there is value to it.  But, I found scripts on the web that used the bitwise operation piece but a lot of repetitive code to determine each Weekday and month.

An alternative would be to perform a query against the msdb database since Scheduled reports are done via a SQL Agent job.  I hope you find this report useful and that you can put it to use.