May 20, 2009 at 4:01 pm
Hi, I would like to extract a job details report from a server with the following format. I intend to do it with something like a cursor, where the list of jobs and details of the server are reported, would anyone be able to help me with the queries required.
Sample report:
Job Name:
Description:
Run By: owner
Schedule :
Log File: Log path
Related Files: files involved in the job steps like batch files
May 20, 2009 at 6:47 pm
Here is the script which gives you all the information that you are looking for.
set nocount on
if object_id('tempdb..##temp_schedule') is not null
begin
drop table ##temp_schedule
--print '##temp_schedule table dropped'
end
if object_id('tempdb..#temp1') is not null
begin
drop table #temp1
end
if object_id('tempdb..#notifications') is not null
begin
drop table #notifications
end
declare @sql varchar(1000)
if (select convert(varchar(50),serverproperty('ProductVersion'))) like '8.00%'
begin
set @sql ='create table ##temp_schedule(
schedule_id int,
schedule_name sysname,
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_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description varchar(4000),
next_run_date int,
next_run_time int)'
end
else
begin
set @sql ='create table ##temp_schedule(
schedule_id int,
schedule_name sysname,
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_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description varchar(4000),
next_run_date int,
next_run_time int,
schedule_uid UniqueIdentifier,
job_count int)'
end
exec (@sql)
declare @jobtable table (jobid int identity,job_id varchar(200))
insert into @jobtable(job_id)
select job_id from msdb.dbo.sysjobs
declare @minjobid int,@maxjobid int,@job_id varchar(200)
select @minjobid = min(jobid) from @jobtable
select @maxjobid = max(jobid) from @jobtable
while(@minjobid <= @maxjobid)
begin
select @job_id = job_id from @jobtable where jobid = @minjobid
set @sql = 'insert into ##temp_schedule '+ char(13) + char(10) +'exec msdb.dbo.sp_help_jobschedule @job_id ='''+@job_id+''',@include_description=1'
exec(@sql)
set @minjobid = @minjobid +1
end
select s3.name JobName,s1.schedule_name,s3.date_created [JobCreatedDate],s3.date_modified[JobLastModified],s3.description[JobDescription],
SUSER_SNAME(s3.owner_sid) JobOwner,
s2.job_id JobID ,s1.schedule_id,s1.schedule_description Description,
s3.enabled into #temp1 from ##temp_schedule s1 inner join msdb.dbo.sysjobschedules s2
on s1.schedule_id = s2.schedule_id
inner join msdb.dbo.sysjobs s3
on s2.job_id = s3.job_id
--where s1.enabled =1
select s1.job_id[JobID],s1.name JobName,s1.date_created [JobCreatedDate],s1.date_modified[JobLastModified],
substring(s2.name,1,100) [CurrentNotification],s2.email_address [OperatorEmailAddress],
case when s1.enabled = 1 then 'Enabled'
when s1.enabled = 0 then 'Disabled'
end as JobStatus
into #notificationsfrom msdb.dbo.sysjobs s1 left outer join msdb.dbo.sysoperators s2
on s2.id = s1.notify_email_operator_id
select convert (varchar(30),serverproperty('ServerName')) ServerName,convert(varchar(120),coalesce(s1.JobName,s2.JobName))JobName,
s1.JobStatus,
s2.JobDescription ,s2.JobOwner,
s2.step_name StepName,s2.command Command,s2.OutPutFileName,
s1.JobCreatedDate,s1.JobLastModified,s2.LastRunDate,s2.LastRunTime,
case
when s2.ScheduleName is null then 'No Schedule'
else
s2.ScheduleName
end [ScheduleName],
case
when s2.JobScheudleDescription is null then 'No Schedule'
else
s2.JobScheudleDescription
end [JobScheduleDescription]
,s1.CurrentNotification,s1.OperatorEmailAddress
from #notifications s1 left outer join
(
select distinct s2.job_id[JobID],substring(replace(s1.JobName,',','-'),1,200)JobName,
s2.step_name,s2.command,substring(replace(s1.schedule_name,',','-'),1,200) ScheduleName,
substring(replace(s1.JobRunDescription,', ','-'),1,200) JobScheudleDescription,s1.LastRunDate,s1.LastRunTime,
s1.JobDescription ,s1.JobOwner,s2.output_file_name OutPutFileName
from msdb.dbo.sysjobsteps s2 inner join
(
select substring(s1.JobName,1,100)[JobName],s1.schedule_name,s1.JobID,s1.JobCreatedDate,
s1.JobLastModified,s2.LastStartDate LastRunDate,s2.LastRunTime,
s1.Description [JobRunDescription],s1.JobDescription ,s1.JobOwner
--, s3.CurrentNotification, s3.OperatorEmailAddress
from #temp1 s1 full outer join
(
select distinct s1.job_id JobID,s1.name JobName,s2.run_date LastStartDate,s2.run_time LastRunTime
from msdb.dbo.sysjobs s1 left outer join msdb.dbo.sysjobhistory s2
on s1.job_id = s2.job_id where s2.run_date=(select max(run_date) run_date from msdb.dbo.sysjobhistory s3
where s3.job_id = s2.job_id
)
and s2.run_time = (select max(run_time) from msdb.dbo.sysjobhistory s4 (nolock)
where s2.job_id = s4.job_id
and run_date = (select max(run_date)from msdb.dbo.sysjobhistory (nolock)
where s4.job_id = job_id )
)
) as s2 on s1.JobID = s2.JobID
) as s1on s1.JobID = s2.job_id
)as s2 on s1.JobID= s2.JobID
--and s1.JobStatus ='Disabled'
order by JobName asc
--select * from #notifications
--select * from msdb.dbo.sysjobschedules
--select count(*) from msdb.dbo.sysjobs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply