June 4, 2012 at 12:14 pm
Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something?
If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?
June 4, 2012 at 12:24 pm
Don't you have the choice of 'Hour' as a schedule?
you can set the start time and interval.
nvm, i see you want an end time.....
June 5, 2012 at 7:46 am
I guess the only way to do this is to add multiple daily subscriptions which run at X hour, so for 8-5, I would have 1 subscription that runs daily at 8, one that runs daily at 9, etc.... cumbersome.
If there was a way to script the "pause" of shared subscriptions, I could set an hourly schedule and then run an agent job to "pause" it at night and then "resume" it in the morning, but even that seems like a bit of a hack job.
June 5, 2012 at 8:16 am
NJ-DBA (6/5/2012)
I guess the only way to do this is to add multiple daily subscriptions which run at X hour, so for 8-5, I would have 1 subscription that runs daily at 8, one that runs daily at 9, etc.... cumbersome.If there was a way to script the "pause" of shared subscriptions, I could set an hourly schedule and then run an agent job to "pause" it at night and then "resume" it in the morning, but even that seems like a bit of a hack job.
Yeah, it would have to be a hack job. Personally, I would create a little stored proc (or insert a section of script into a stored proc for the report, whichever fits you better) that can be called with the report. This SP would check the time, and if it is the 5 o'clock run, it will update the schedule begin time from 8am today to 8am tomorrow. I would not have an end time.
Jared
CE - Microsoft
July 19, 2013 at 8:03 am
I know this is a really old thread, but I ran into this same problem again and wanted to share the solution I'm using. Essentially, what I did is scheduled the report to run once, then created a SQL Agent job which calls the job that was created for that one time execution. Here's the three steps to accomplish this:
First, schedule the report to run one time.
Second, create a few that shows the scheduled reports and associated SQL Agent Jobs:
create view [dbo].[vw_scheduled_Reports]
as
select
Schedule.ScheduleID as SQLAgent_Job_Name, [Catalog].Name as reportname,
Subscriptions.Description as sub_desc,
Subscriptions.DeliveryExtension as sub_delExt,
[catalog].path as reportpath
from reportserver.dbo.reportschedule inner join reportserver.dbo.Schedule
on ReportSchedule.ScheduleID = Schedule.ScheduleID
inner join reportserver.dbo.Subscriptions
on ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
inner join reportserver.dbo.[Catalog]
on ReportSchedule.ReportID = [Catalog].ItemID
and Subscriptions.Report_OID = [Catalog].ItemID
GO
Third, scheduled a SQL job to run on the desired schedule (in my case M-F 8am-5pm) which executes the following TSQL:
declare @counter int
Set @counter=1
declare @sqlagentjobname uniqueidentifier
declare @reportstable table (sqlagentjobname uniqueidentifier,row int)
insert @reportstable
SELECT SQLAgent_Job_Name, ROW_NUMBER() over (order by reportname) as row
FROM [ReportServer].[dbo].[vw_scheduled_Reports] a
where reportname in
(
'MYREPORTNAME',
'MYOTHERREORTNAME'
)
While @counter <= (select COUNT (*) from @reportstable)
begin
select @sqlagentjobname=sqlagentjobname from @reportstable where row=@counter
exec msdb.dbo.sp_start_job @job_name=@sqlagentjobname
set @counter=@counter+1
end
I thought this was a clever solution to the problem so I thought I'd share.
July 19, 2013 at 9:18 am
At this point, I have often used:
IF (SELECT DATEPART(HOUR,GETDATE())) BETWEEN 5 AND 17
BEGIN
EXEC database.dbo.storedProcedure
END
Jared
CE - Microsoft
July 19, 2013 at 10:08 am
In my case, I'm using reporting services subscriptions to mail out the report. So I didnt want to use that logic in a stored proc because once the report is called, it's going to mail itself out. In that case, the proc run wouldn't return a result set... so I'm not sure what would happen... maybe the report would mail out an empty result or maybe a error would get logged... for me it was better to just run the report on the hours I need want it sent.
August 30, 2013 at 7:47 am
NJ-DBA (7/19/2013)
In my case, I'm using reporting services subscriptions to mail out the report. So I didnt want to use that logic in a stored proc because once the report is called, it's going to mail itself out. In that case, the proc run wouldn't return a result set... so I'm not sure what would happen... maybe the report would mail out an empty result or maybe a error would get logged... for me it was better to just run the report on the hours I need want it sent.
I do this a lot and just use RAISEERROR in the SP if the result set is empty.
So I do
If (Select count(*) FROM Table WHERE X = Y) > 0 and @FailOnNoRecords = 1
BEGIN
RAISEERROR....
END
SELECT * FROM Table Where X = Y
I use the @FailOnNoRecords as a hidden param (with default of 0) in the SSRs report so users don't see an error if they run the report manually
Of course it's inefficient as you run the query once to find out if there any records and once again to return results if there are. Works for some utility jobs though
The other disadvantage is that it just says an error occurred in subscription list, so you have to know it's your error
September 23, 2013 at 1:10 am
Thats probably the most K.I.S.S answer i've seen so far.
Brilliant!
June 16, 2014 at 7:35 pm
Awesome! Just what I needed 🙂
Couldn't be more straight forward that this...
Thanks for posting this !
July 16, 2014 at 7:44 pm
Great solution !
I used it in combination with data driven reports to split a large amount in smaller batches and works like a charm !
Now it is easy to manage the creation of the reports without running low in resources.
Two thumbs up !
October 8, 2014 at 3:24 pm
Thanks a lot for this tip - I ran into the same problem where I have a report that monitors Credit Card payments and triggers if there is none in 2 minutes - however I wanted to raise the threshold to 30 minutes overnight - this solution worked perfectly as I created 2 subscriptions with different parameters and then amended the start and end times at the SQL Agent Level.
July 14, 2015 at 1:25 pm
Brilliant! This just saved my bacon. Thank you.
November 5, 2015 at 12:28 pm
Hi
I just wanted to let you know that this saved me from upgrading sql to enterprise just to run a data driven subscription. This was a brilliant out of the box work around to a problem that Microsoft has. Doing a simple subscription with an end time should be part of the program.
Genius!!
Vic
President
Affordable-IT
February 6, 2019 at 10:44 am
This is great. I was struggling with scheduling reports to every 30 minutes within a set time frame each day. This gives me so much more flexibility. Glad to have this in my tool belt! Thanks!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply