May 15, 2008 at 9:04 am
Hi,
Does anyone know the TSQL to query the reportServer DB to see which current subscriptions are scheduled for the next day, week, month? For SQL 2000 & 2005 (if poss)
Any help would be greatly appreciated as it would help an impending upgrade.
Thanks.
May 15, 2008 at 9:25 am
This may likely get you more (or less) than you want but it will set you in the right direction with the tables and columns to query. Hope it helps.
SELECT DISTINCT c.Name AS RptName,
sc.Name AS SchedName,
su.LastStatus,
su.LastRunTime AS SubLast,
sc.LastRunTime AS SchedLast,
sc.NextRunTime,
MinutesInterval as MinInt,
DaysInterval as DaysInt,
WeeksInterval as WeeksInt,
Month,
DaysOfWeek,
MonthlyWeek,
DaysOfMonth,
sc.RecurrenceType,
c.Path
FROM dbo.Catalog AS c INNER JOIN
dbo.ReportSchedule AS rs ON c.ItemID = rs.ReportID INNER JOIN
dbo.Schedule AS sc ON rs.ScheduleID = sc.ScheduleID INNER JOIN
dbo.Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID
WHERE sc.NextRunTime > GetDate())
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
May 15, 2008 at 10:24 am
Thanks for that. Looks good, however the nextruntime in the schedule table is NULL. could there be any obvious reason for this?
Thanks.
May 15, 2008 at 11:56 am
That's apparently the case when running subs as individual jobs (i.e. Timed Subscription) and not on Shared Schedules. At least that's when I get NULL values.
Unfortunately, if you aren't able to move your subs to Shared Schedules, I think you'll have to start figuring out and coding for the numbers that populate the MinutesInterval, DaysInterval, WeeksInterval, Month, DaysOfWeek, MonthlyWeek, DaysOfMonth,
sc.RecurrenceType columns.
here's some of it to get you started:
Sun = 1, Mon = 2, Tues = 4, Weds = 8, Th = 16, Fri = 32, Sat = 64
Do you see how the system works? From there you can determine which days the sub runs.
Monday only = 2, M-F = 62 (2+4+8+16+32), Su-Sa = 127 (62(M-F)+1(Su)+64(Sa)).
Each of the date parts work the same. For month it's Jan=1, Feb=2, Mar=4, etc.
It gets me real twitchy when it comes to calendar dates. 1st=1, 2nd=2, 3rd=4, 4th=8, 5th=16...allthe way to 31st. I have one report that runs the 1st, 7th, 15th, 21st...the number for that is 2113665!
If it were me, I'd start creating Shared Schedules and modifying the subs to use them.
Good luck with this. Hopefully, someone will jump in with a better solution for you.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
May 15, 2008 at 2:47 pm
Thanks for the response again, clear and concise. Unlike the planning for using timed subscriptions!!! You are correct, the reports that have nulls are timed and the one that has a next run time has a date of some description.
Is it easy to change the subscription type? (To shared schedules) By your response it seems like it could be a convoluted task??
Whats the difference between timed and shared and what are the risks or disadvantages of trying to migrate the current subscriptions??? Alternatively, are there any benefits (apart from the leading qu) that I would gain by changing the subscription type.
Thanks
May 15, 2008 at 3:20 pm
My understanding of the advantages of shared vs. timed is primarily the server resources involved. Here is a comparison of the two prepared by one of our DBAs.
"Shared schedules are multipurpose items that contain ready-to-use schedule information. You create a shared schedule once, and then reference it in a subscription or property page when you need to specify schedule information.
Report-specific schedules are defined in the context of an individual report, subscription, or report execution operation to determine cache expiration or snapshot updates. These schedules are created inline when you define a subscription or set report execution properties. Every single report-specific schedule will create one SQL job on SQL server side."
The mechanics of changing from timed to shared is not difficult on its face. What'll make it cumbersome is if you have a large number of reports/subs to modify and many different shared schedules to create.
So, first, how many subs are we talking about? At the very least, each sub will have to be opened to switch from timed to shared.
Second, are the scheduled times widely varying? If most run at nearly the same time, you may only need to create a few.
Answers to these questions will let me know better how to guide you further.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
June 4, 2008 at 5:00 am
Hi,
Many thanks for the info, extremely useful!! I have however run into a further problem during the upgrade and noticing various message boards you seem like the report server guru!!
The problem is this, I have a report server 2000 with approx 800 subscriptions that has now been upgraded to 2005, all seemed to go well until a few started to fail with the following message:
Failure sending mail: An internal error occurred on the report server
This does not occur on all the subscriptions, as we have proof that it has emailed some reports. I have used your query to confirm this.
There is also a couple of errors in the event log stating:
Report Server (MSSQLSERVER) cannot load the Report Server DocumentLibrary extension.
I'm not sure if this has anything to do with the problem?
If you require any further info please let me know
Your help would be appreciated!!!
June 4, 2008 at 8:44 am
I posed your situation to our DBAs. Here's their reponse and a little further explanation.
"OK. He can try the following:
1. Ensure that smtp mail (SQL Mail) works and is running
2. Since he’s now using 2005 – he should really start using DBMail – I can almost guarantee that this is when he started noticing the errors (after he upgraded)"
And
"In SQL 2000 – it’s smtp mail (SQL Mail) that you set up that runs dependent on the SQL Agent.
In SQL 2005 – it (DBMail) is a separate item that uses exchange to work (it can work by resolving names, but works better if you put the whole address in)"
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
June 4, 2008 at 11:04 am
Thanks for the quick response, the problem seems more with the report server or with the configuration than with the smtp.
The reason(s) why I say this is that:
Some subscriptions have actually worked and successfully mailed out
We have another report server (pure SQL 2005) that is working fine running sched reports
Looking at our mail server is showing that when mails have failed, they haven't even touched the mail server
The only difference between our two 2005 servers (one that works and the one that doesn't) is that 1 has been upgraded from 2000.
What would be great is if I could run a report and step through so that I can see where the error occurs??? Really puzzling!!!
June 4, 2008 at 12:06 pm
Found this on MSDN, maybe will help?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2656661&SiteID=1
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply