|
vijays3
Constraint Violating Yak Guru
354 Posts |
 Posted - 2010-05-11 : 13:46:23
|
| Hi Experts ,I have created a script for job report which runs on all version of sql server.I want to add two more columns last_run_date and next_run_dateI tried to add this columns and retrive the data by using thisfrom (#table1 a RIGHT OUTER JOIN msdb..sysjobs b ON a.job_id = b.job_id LEFT OUTER JOIN #temp c on a.name = c.jobname and a.job_id = c.Job_id) JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = a.job_id But it is generating around 2000 rows although i have only 15 jobs Please check my script and help me to add this two coloums so that i may know the last_run_date and next_run_date of job as well. --execute jobreports alter procedure jobreports as declare @cmpt int select @cmpt = cmptlevel from sysdatabases where name ='master' create table #table1 ( schedule_id int, job_id varchar(1000) , name varchar(100), enabled int, freq_type int, freq_interval int, freq_subday_type int, freq_subday_interval int, freq_relative_interval int, freq_recurrence_factor int, active_start_date int, active_start_time int, active_end_time int, active_end_date int, date_created datetime, date_modified datetime ) if(@cmpt<=80) begin declare @sql varchar(8000) set @sql ='insert into #table1 select schedule_id , job_id , name, enabled,freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, active_start_date, active_start_time, active_end_time, active_end_date, date_created,null from msdb..sysjobschedules' exec (@sql) end else begin insert into #table1 select a.schedule_id, a.job_id , b.name, b.enabled,b.freq_type, b.freq_interval, b.freq_subday_type, b.freq_subday_interval, b.freq_relative_interval, b.freq_recurrence_factor, b.active_start_date, b.active_start_time, b.active_end_time, b.active_end_date, b.date_created,b.date_modified from msdb..sysjobschedules a, msdb..sysschedules b where a.schedule_id = b.schedule_id end declare @schedule_id varchar(100),@job_id sysname, @freq_interval int, @jobname varchar(50),@freq_type int declare @days varchar(100), @day varchar(10) declare @x int, @y int, @z int declare @counter smallint create table #temp (job_id varchar(50), jobname sysname, Jdays varchar(100)) declare job_cur cursor local static for select job_id,name ,freq_interval from #table1 where freq_type = 8 open job_cur fetch next from job_cur into @job_id, @jobname,@freq_interval while @@fetch_status = 0 begin set @counter = 0 set @x = 64 set @y = @freq_interval set @z = @y set @days = '' set @day = '' while @y <> 0 begin --declare @x int ,@y int --select @x=64,@y=1 select @y = @y - @x --print @y select @counter = @counter + 1 If @y < 0 Begin set @y = @z End else Begin Select @day = CASE @x when 1 Then 'Sunday' when 2 Then 'Monday' when 4 Then 'Tuesday' when 8 Then 'Wednesday' when 16 Then 'Thursday' when 32 Then 'Friday' when 64 Then 'Saturday' End select @days = @day + ',' + @days end Select @x = CASE @counter When 1 then 32 When 2 then 16 When 3 then 8 When 4 then 4 When 5 then 2 When 6 then 1 End set @z = @y if @y = 0 break end Insert into #temp select @job_id, @jobname, left(@days, len(@days)-1) fetch next from job_cur into @job_id, @jobname,@freq_interval End close job_cur deallocate job_cur select b.name Job_Name, CASE b.enabled when 1 then 'Enabled' Else 'Disabled' End as JobEnabled, a.name Schedule_Name, CASE a.enabled when 1 then 'Enabled' Else 'Disabled' End as ScheduleEnabled, CASE freq_type when 1 Then 'Once' when 4 Then 'Daily' when 8 then 'Weekly' when 16 Then 'Monthly' --+ cast(freq_interval as char(2)) + 'th Day' when 32 Then 'Monthly Relative' when 64 Then 'Execute When SQL Server Agent Starts' End as [Job Frequency], CASE freq_type when 32 then CASE freq_relative_interval when 1 then 'First' when 2 then 'Second' when 4 then 'Third' when 8 then 'Fourth' when 16 then 'Last' End Else '' End as [Monthly Frequency], CASE freq_type when 16 then cast(freq_interval as char(2)) + 'th Day of Month' when 32 then CASE freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Weekday' when 10 then 'Weekend day' End when 8 then c.Jdays Else '' End as [Runs On], CASE freq_subday_type when 1 then 'At the specified Time' when 2 then 'Seconds' when 4 then 'Minutes' when 8 then 'Hours' End as [Interval Type], CASE freq_subday_type when 1 then 0 Else freq_subday_interval End as [Time Interval], CASE freq_type when 8 then cast(freq_recurrence_factor as char(2)) + ' Week' when 16 Then cast(freq_recurrence_factor as char(2)) + ' Month' when 32 Then cast(freq_recurrence_factor as char(2)) + ' Month' Else '' End as [Occurs Every], left(active_start_date,4) + '-' + substring(cast(active_start_date as char),5,2) + '-' + right(active_start_date,2) [Begin Date-Executing Job], left(REPLICATE('0', 6-len(active_start_time)) + cast(active_start_time as char(6)),2) + ':' + substring(REPLICATE('0', 6-len(active_start_time)) + cast(active_start_time as char(6)),3,2) + ':' + substring(REPLICATE('0', 6-len(active_start_time)) + cast(active_start_time as char(6)),5,2) [Executing At], left(active_end_date,4) + '-' + substring(cast(active_end_date as char),5,2) + '-' + right(active_end_date,2) [End Date-Executing Job], left(REPLICATE('0', 6-len(active_end_time)) + cast(active_end_time as char(6)),2) + ':' + substring(REPLICATE('0', 6-len(active_end_time)) + cast(active_end_time as char(6)),3,2) + ':' + substring(REPLICATE('0', 6-len(active_end_time)) + cast(active_end_time as char(6)),5,2) [End Time-Executing Job], b.date_created [Job Created], a.date_created [Schedule Created], ja.run_requested_date as last_run_date --,ja.next_scheduled_run_date from (#table1 a RIGHT OUTER JOIN msdb..sysjobs b ON a.job_id = b.job_id LEFT OUTER JOIN #temp c on a.name = c.jobname and a.job_id = c.Job_id) JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = a.job_id Order by 1 Drop table #table1 Drop Table #Temp --Drop table jobs GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO |
|
add columns