July 22, 2010 at 9:01 pm
Hi All,
Is there any way to retrieve the "schedule date/time" detail of subscriptions?
I am able to select the report-name, path, email-list and recurrence type of subscriptions,
I want to retrieve the running date, time of it as well.
Can anyone help? Thanks a lot.
KK
July 27, 2010 at 6:31 am
You need to connect to the ReportServer DB on the report server to retrieve the Running time of the subscriptions...
select * from subscriptions
Prashant Bhatt
Sr Engineer - Application Programming
July 27, 2010 at 6:33 am
LastRuntime is the column which can help getting the Running time value of the subscription
Prashant Bhatt
Sr Engineer - Application Programming
July 28, 2010 at 2:25 am
I found a solution. It can export the necessary schedule date/time for me.
SELECT
TOP (100) PERCENT cat.Name AS 'ReportName',
rs.ScheduleID AS 'SQLJobID',
CASE s.recurrencetype
WHEN 1 THEN 'Once'
WHEN 3 THEN
CASE s.daysinterval
WHEN 1 THEN 'Every day' ELSE 'Every other ' + CAST(s.daysinterval AS varchar)
+ ' day.' END WHEN 4 THEN CASE s.daysofweek WHEN 1 THEN 'Every '
+ CAST(s.weeksinterval AS varchar)
+ ' week on Sunday' WHEN 2 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Monday' WHEN 4 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Tuesday' WHEN 8 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Wednesday' WHEN 16 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Thursday' WHEN 32 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Friday' WHEN 64 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Saturday' WHEN 42 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on Monday, Wednesday, and Friday' WHEN 62 THEN 'Every '
+ CAST(s.weeksinterval AS varchar)
+ ' week on Monday, Tuesday, Wednesday, Thursday and Friday' WHEN 126 THEN 'Every '
+ CAST(s.weeksinterval AS varchar)
+ ' week from Monday to Saturday' WHEN 127 THEN 'Every ' + CAST(s.weeksinterval AS varchar)
+ ' week on every day' END WHEN 5 THEN CASE s.daysofmonth WHEN 1 THEN 'Day '
+ '1' + ' of each month' WHEN 2 THEN 'Day ' + '2' + ' of each month'
WHEN 4 THEN 'Day ' + '3' + ' of each month' WHEN 8 THEN 'Day ' + '4' + ' of each month' WHEN 16 THEN 'Day ' + '5' + ' of each month' WHEN 32 THEN 'Day ' + '6' + ' of each month' WHEN 64 THEN 'Day ' + '7' + ' of each month' WHEN 128 THEN 'Day ' + '8' + ' of each month' WHEN 256 THEN 'Day ' + '9'
+ ' of each month' WHEN 512 THEN 'Day ' + '10' + ' of each month' WHEN 1024 THEN 'Day ' + '11' + ' of each month' WHEN 2048 THEN 'Day ' + '12' + ' of each month' WHEN 4096 THEN 'Day ' + '13' + ' of each month' WHEN 8192 THEN 'Day ' + '14' + ' of each month' WHEN 16384 THEN 'Day ' + '15' + ' of each month' WHEN 32768 THEN 'Day ' + '16' + ' of each month' WHEN 65536 THEN 'Day ' + '17' + ' of each month' WHEN 131072 THEN 'Day ' + '18' + ' of each month' WEN 262144 THEN 'Day ' + '19' + ' of each month' WHEN 524288 THEN 'Day ' + '20' + ' of each month' WHEN 1048576 THEN 'Day ' + '21' + ' of each month'
WHEN 2097152 THEN 'Day ' + '22' + ' of each month' WHEN 4194304 THEN 'Day ' + '23' + ' of each month' WHEN 8388608 THEN 'Day ' + '24' + ' of each month'
WHEN 16777216 THEN 'Day ' + '25' + ' of each month' WHEN 33554432 THEN 'Day ' + '26' + ' of each month' WHEN 67108864 THEN 'Day ' + '27' + ' of each month'
WHEN 134217728 THEN 'Day ' + '28' + ' of each month' WHEN 268435456 THEN 'Day ' + '29' + ' of each month' WHEN 536870912 THEN 'Day ' + '30' +
' of each month' WHEN 1073741824 THEN 'Day ' + '31' + ' of each month' END WHEN 6 THEN 'The ' + CASE s.monthlyweek WHEN 1 THEN 'first' WHEN
2 THEN 'second' WHEN 3 THEN 'third' WHEN 4 THEN 'fourth' WHEN 5 THEN 'last' ELSE 'UKNOWN' END + ' week of each month on ' + CASE s.daysofweek
WHEN 2 THEN 'Monday' WHEN 4 THEN 'Tuesday' ELSE 'Unknown' END ELSE 'Unknown' END + ' at ' + LTRIM(RIGHT(CONVERT(varchar, s.StartDate, 100), 7))
AS 'ScheduleDetails', s.RecurrenceType, s.MinutesInterval AS 'MinsInterval', s.DaysInterval, s.WeeksInterval, s.DaysOfWeek, s.DaysOfMonth,
s.MonthlyWeek, cat.Path AS 'ReportPath'
FROM dbo.Catalog AS cat INNER JOIN
dbo.ReportSchedule AS rs ON cat.ItemID = rs.ReportID INNER JOIN
dbo.Schedule AS s ON rs.ScheduleID = s.ScheduleID INNER JOIN
dbo.Subscriptions AS sub ON sub.SubscriptionID = rs.SubscriptionID
WHERE (LEN(cat.Name) > 0)
ORDER BY 'ReportName'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply