Need to to combine the script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Need to to combine the script

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-05-07 : 05:40:51

Hi All

I have a problem writing a job report script. I want to create a script
which could generate the job report from any of the version(2000,2005) of sql.I have one script which works fine on sql 2005.I have created one script and Problems are tables availaleble in the version

sql-2005
msdb..sysjobsschedules
msdb..sysschedule

two tables having a required informatiom

sql 2000
msdb..sysjobschedules
There is no msdb..sysschedule table

And find my script..and i want to execute it on the basis of compatibility.

declare @cmpt int
select @cmpt = cmptlevel from sysdatabases where name ='master'
print @cmpt
if(@cmpt=90)
begin
drop table jobs
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 into jobs
from msdb..sysjobschedules a, msdb..sysschedules b
where a.schedule_id = b.schedule_id
goto here
end
else
begin
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,b.date_modified into jobs
from msdb..sysjobschedules

goto here
end
end
here:

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 jobs 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
GOTO start
End

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
start:
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]
from jobs 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

Order by 1

Drop Table #Temp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources