SQL Server Performance

scheduled report next execution date.

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

  1. Anirban Biswas New Member

    I am trying to schedule a report from Report Manager.
    What SSRS does is, it creates a job with the help of the SQL Agent and schedules it to the respective date. The dates are calculated as per the server local time.
    Report server has a table named Schedule which has an entry for the report and the ScheduleID is the name of the job that got created. So, ReportServer.dbo.ScheduleID = MSDB.DBO.SysJobs.Name
    Now, MSDB has a table named MSDB.dbo.SysJobActivity. The column named 'next_scheduled_run_date' is the next run date of the job or the report.
    The query that I have used is as below...
    Select S.ScheduleID, JA.next_scheduled_run_date
    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)

    Can any one tell me if I am looking at the right location for the next execution date??
    Or is there any other location which I am missing out.
  2. satya Moderator

    That pertains to jobs only - as that table is for "Records current SQL Server Agent job activity and status."
  3. Anirban Biswas New Member

Share This Page