SQL Server Performance

Next Execution Date Error in SSRS 2005

Discussion in 'SQL Server 2005 Reporting Services' started by Anirban Biswas, Mar 7, 2008.

  1. Anirban Biswas New Member

    Hi,
    I am trying to schedule a report from Report Manager Interface (SSRS 2005) as below:
    Scenario 1:
    Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy)
    End Date: 31-dec-2009
    Schedule: Monthly (Current Month) – Days: [any day in the Current Month which is < getdate()] e.g: March – 4th
    Scenario 2:
    Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy)
    End Date: 31-dec-2009
    Schedule: Monthly (Less than Current Month) – Days: [any day] e.g: Feb – 12th
    Scenario 3:
    Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy)
    End Date: 31-dec-2009
    Schedule: Monthly (Grater than Current Month) – Days: [any day] e.g: May – 10th
    In all the above scenarios we observed a strange behavior of SSRS.
    Scenario 1: Report is scheduled on 4th April 2008 where as it should be 4th March 2009.
    Scenario 2: Report is scheduled on 12th Feb 2009 as expected.
    Scenario 3: Report is scheduled on 10th May 2009 as expected.
    Now, we changed the end date (31st Dec 2008) of scenario 1 as below:
    Scenario 1:
    Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy)
    End Date: 31-dec-2008
    Schedule: Monthly (Current Month) – Days: [any day in the Current Month which is < getdate()] e.g: March – 4th
    The next execution date is again 4th April 2008, which is wrong.
    After scheduling the above reports we executed the below query in the ReportServer DB (SQL Prompt)
    SELECT S.ScheduleId AS ScheduleId
    , S.LastRunTime AS LastRunTime
    , JA.Next_Scheduled_Run_Date AS NextRunTime
    , RecurrenceType = CASE S.RecurrenceType
    WHEN 1 THEN 'Once'
    WHEN 2 THEN 'Hourly'
    WHEN 3 THEN 'Daily'
    WHEN 4 THEN 'Weekly'
    WHEN 5 THEN 'Monthly'
    WHEN 6 THEN 'Monthly'
    END
    FROM ReportServer.dbo.Schedule S WITH (NOLOCK)
    Inner Join (
    select j.name,max(ja.next_scheduled_run_date) as next_scheduled_run_date
    from MSDB.dbo.SysJobActivity as ja WITH (NOLOCK)
    inner join MSDB.dbo.SysJobs j WITH (NOLOCK) on ja.Job_ID = j.Job_ID
    where ja.next_scheduled_run_date is not null and ja.next_scheduled_run_date >= Getdate()
    group by j.name
    ) as JA on convert(nvarchar(256),S.ScheduleId) = convert(nvarchar(256),JA.name)
    Where JA.name = (select top 1 name from msdb.dbo.sysJobs order by Date_Created desc )
    -- Where JA.name in (select top 10 name from msdb.dbo.sysJobs order by Date_Created desc )

    We have tried the same scenarios using the SSRS APIs. But unfortunately we received the same results.
    Did anyone face this type of issue in past?
    Is this a SSRS BUG or are we missing any point?
  2. dineshasanka Moderator

    think next date of the execution should be 4th of April as start date of the schedule is 4th of March. So I can't see why it is wrong

Share This Page