Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Day of Month subscription for Shared SSRS schedule


Last Day of Month subscription for Shared SSRS schedule

Author
Message
Angela Henry
Angela Henry
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 285
I need to create a shared schedule in SSRS 2005 for the last day of the month. Not the last Friday, Monday, Sunday, etc., but the last day of the month. This option does not appear to be be available. Anybody figure out why and a work around for it?

Thanks
-A.
Angela Henry
Angela Henry
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 285
An update on this.

I thought I had figured out how to do this. I set up a total of three separate shared schedules (not bothering with leap year at this point),

1 - on the 30th day of April, June, September & November
2 - on the 31st day of January, March, May, July, August, October & December
3 - on the 28th day of February

However this doesn't work as I expected because I'm still getting schedules executed on the 30th day of the month in months that have 31 days.

Again, any insight would be helpful. I can't believe that I'm the only one that's trying to figure this out.
jlane-602614
jlane-602614
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 61
i did the exact same thing and have the same problem. i havent found an answer yet.
Angela Henry
Angela Henry
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 285
What I had tried before was to create a managed schedule through SSRS, scheduling it for the last Friday of the month. Then I went to the SQL Server Agent and modified it to run on the last day of the month. When I did this before it didn't work and caused a fatal error within SSRS.

What I discovered is that you also have to modify the the next run time column in the SSRS table to make it work. Specifically, you need to modify the dbo.Schedule.NextRunTime column to really be the next run time,

Example:
On 10/31/2008, I created a shared schedule to run on the last Friday of the month at 10pm in SSRS, I changed the start date of the schedule to 11/1/2008, which made the next run time 11/28/2008 @ 10pm. I then went into the SQL Server Agent job that SSRS subsequently created and modified the job to run on the last day of the month (now the next run time listed in SQL Server Agent is 11/30/2008 @ 10pm).

I then went into the ReportServer database on my SSRS server and modified the dbo.Schedule.NextRunTime column for this schedule from 11/28/2008 @ 10pm to 11/30/2008 @ 10pm. This now matches what's in the SQL Server Agent job and it passes the consistency check that SSRS runs.

Hope this helps.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45386 Visits: 39940
Angela Henry (10/31/2008)
An update on this.

I thought I had figured out how to do this. I set up a total of three separate shared schedules (not bothering with leap year at this point),

1 - on the 30th day of April, June, September & November
2 - on the 31st day of January, March, May, July, August, October & December
3 - on the 28th day of February

However this doesn't work as I expected because I'm still getting schedules executed on the 30th day of the month in months that have 31 days.

Again, any insight would be helpful. I can't believe that I'm the only one that's trying to figure this out.


Do you mean schedule jobs? Or ???

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Angela Henry
Angela Henry
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 285
Shared schedules ~ SSRS
Scheduled jobs ~ SQL Server Agent jobs
jlane-602614
jlane-602614
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 61
I found this link and this should work. http://developmentnow.com/g/115_2005_11_0_0_629283/Scheduling-outside-SRS.htm

You basically create a phony subscription then go to the reporting services database on that server. Get the information you need from the subscriptions table. run dbo.addevent with the correct values and it will run the report.

So i put this logic into a stored procedure that checks if its the last day of the month then it will run the report.

Now just schedule a job to run daily.
UKGav
UKGav
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 231
Does anybody have any information, links or experience on expanding on / editing the SSRS Scheduling GUI or the ReportingServices.dbo.Schedules / ReportSchedule tables in SSRS 2005?

I think it's plain lazyness that Microsoft didn't give SSRS the same quality scheduling options that you get with the SSMS Agent!
kcooper
kcooper
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 122
Maybe this has already been said in a different way, but this is what I've done, and I think it works -- please feel free to try it out and post your findings.

I created a table SchedTable of dates that I need a data-driven SSRS report to go out, and set the query for the recipient list, fields, etc to:

SELECT 'me@awesome.com' as EmailAddress, 'blah' as parameter1, EmailDate from SchedTable where convert(varchar(10), getdate(),102) = EmailDate



I scheduled this to run daily in the SSRS scheduler, but since the query only returns data for the schedule dates in my table, the report is only sent out on the dates I want.
consult989
consult989
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 150
I'm not sure why no one else listed this solution but it builds off of using the addevent to explicitly invoke the subscription. Only, instead of building a stored procedure which runs every day, simply add a new schedule to the SQL Agent on the report server which uses a SQL Task to process the addevent. Then you can take advantage of the same scheduling options you have in SQL Agent to invoke the SSRS subscriptions. It's a pretty simple work-around. Make sure you use a descriptive name when creating the new jobs to invoke the SSRS subscriptions however or later it becomes difficult to tell which job invokes which subscription.

You can also use sp_start_job to invoke the schedule created by the SSRS subscription. Either way should work.

You don't need to run an SP every day to check if it's the beginning or end of the month since SQL Agent has these options and will use the addevent to run the subscription at the appropriate time.

One more thing : Do not change the timing of the schedule that SSRS adds as part of the subscription (The one with the GUID name) because it will not work. I found this through trial and error.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search