x

Reporting Services My Subscriptions: Last Day of Month

Is there way to schedule a job in Reporting Services> My Subscriptions to run on the “last” day of the month regardless of the number of days in the month? I set up 4 subscriptions to the same job, one for months with 28 day, 29 days, 30 days or 31 days. I check the appropriate months for each of those days, yet I get reports on day 28 in those months with 30 or 31 days.

more ▼

asked Aug 15, 2013 at 08:06 PM in Default

avatar image

jonlellelid
81 7 9 13

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

As @raadee mentioned you can alter the job created by the schedule, but better way than changing the Agent job for the Schedule is, take the code which the Agent job is calling and create new job. It will be something like:

 exec [reportserverdb].dbo.AddEvent @EventType='TimedSubscription', @EventData ='GUID of the subscription'.

Then create new job with appropriate schedule and simply call that code from your job. once your new job is created alter the subscription so it should be executed only once, but in past. It will update the original agent job so it will never trigger.

Eventually create a Shared Schedule "Disabled" with execution time in past. Such Shared Schedule you can than use to disable the schedules. (You will have single job without schedule in the agent for all such disabled subscriptions).

Creating separate job, is a better solution, because your changes to the job created by SSRS are not guaranteed. SSRS traverses periodically through the subscriptions and aligns the jobs according the subscription specification. With separate job, you will be sure, your schedule will not be altered.

more ▼

answered Aug 16, 2013 at 06:52 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

I deleted my post since your solution is preferred. The "traverses periodically" part makes my solution kind of bad.

Thanks.

Aug 16, 2013 at 06:59 AM raadee
(comments are locked)
10|1200 characters needed characters left

Thanks Pavel. I set a new job up and will see what happens on the last day of August!

more ▼

answered Aug 21, 2013 at 06:53 PM

avatar image

jonlellelid
81 7 9 13

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x9

asked: Aug 15, 2013 at 08:06 PM

Seen: 1368 times

Last Updated: Aug 21, 2013 at 06:53 PM

Copyright 2017 Redgate Software. Privacy Policy