Hi, 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.
That pertains to jobs only - as that table is for "Records current SQL Server Agent job activity and status."
Yes Satya...... u r right..... But in SSRS when you schedule a report it creates a job and then schedules accordingly.... Plz go through this link.... http://www.informit.com/articles/article.aspx?p=470593&seqNum=7