Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Last Day of Month subscription for Shared SSRS schedule Expand / Collapse
Author
Message
Posted Friday, May 2, 2008 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
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.
Post #494179
Posted Friday, October 31, 2008 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
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.
Post #594902
Posted Monday, November 3, 2008 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:35 AM
Points: 2, Visits: 58
i did the exact same thing and have the same problem. i havent found an answer yet.
Post #595908
Posted Monday, November 3, 2008 4:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
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.
Post #596202
Posted Monday, November 3, 2008 5:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #596226
Posted Tuesday, November 4, 2008 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
Shared schedules ~ SSRS
Scheduled jobs ~ SQL Server Agent jobs
Post #596634
Posted Tuesday, November 4, 2008 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:35 AM
Points: 2, Visits: 58
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.
Post #596638
Posted Tuesday, March 17, 2009 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 10:08 AM
Points: 34, Visits: 186
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!
Post #677675
Posted Thursday, March 26, 2009 11:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 17, 2010 11:23 AM
Points: 118, 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.
Post #684443
Posted Monday, July 27, 2009 4:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 13, 2011 10:49 AM
Points: 1, 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.
Post #760403
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse