Reporting Services scheduling- hourly between 8 and 5 impossible?

  • NJ-DBA

    SSChampion

    Points: 13832

    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?

  • Geoff A

    SSChampion

    Points: 11407

    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.....

  • NJ-DBA

    SSChampion

    Points: 13832

    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.

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    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.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • NJ-DBA

    SSChampion

    Points: 13832

    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.

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    At this point, I have often used:

    IF (SELECT DATEPART(HOUR,GETDATE())) BETWEEN 5 AND 17

    BEGIN

    EXEC database.dbo.storedProcedure

    END

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • NJ-DBA

    SSChampion

    Points: 13832

    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.

  • mark 4643

    SSCommitted

    Points: 1503

    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

  • malcolm-745586

    SSC Journeyman

    Points: 76

    Thats probably the most K.I.S.S answer i've seen so far.

    Brilliant!

  • BobMcClellan

    Old Hand

    Points: 399

    Awesome! Just what I needed 🙂

    Couldn't be more straight forward that this...

    Thanks for posting this !

  • sergiocarrero

    SSC Journeyman

    Points: 91

    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 !

  • JayK

    SSCrazy

    Points: 2679

    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.

  • Michael 97042

    SSC Enthusiast

    Points: 111

    Brilliant! This just saved my bacon. Thank you.

  • manfredi.victor

    SSC Rookie

    Points: 41

    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

  • tim.blackwell 62060

    Grasshopper

    Points: 11

    NJ-DBA - Friday, July 19, 2013 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

    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 16 total)

You must be logged in to reply to this topic. Login to reply