May 30, 2008 at 6:58 am
in SQL 2000 you could just right mouse click on jobs and click export to a tab delimited file. How do I do this in SQL 2005?
May 30, 2008 at 8:43 am
This is a start that you can build on...it'll show job names, enabled\disabled state for the job and schedule, and the schedule.
Kendal Van Dyke
http://kendalvandyke.blogspot.com/
[font="Courier New"]use msdb
GO
SET NOCOUNT ON
GO
declare@job_name sysname,
@job_enabled bit,
@schedule_enabled bit,
@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 nvarchar(8),
@active_end_time nvarchar(8),
@schedule_description NVARCHAR(255),
@loop INT,
@idle_cpu_percent INT,
@idle_cpu_duration INT
declare @job_info table (
job_name sysname,
job_enabled bit,
schedule_enabled bit,
schedule_description nvarchar(255)
)
declare curJob cursor local fast_forward for
SELECT sysjobs_view.name,
sysjobs_view.enabled,
sysschedules_localserver_view.enabled,
sysschedules_localserver_view.freq_type,
sysschedules_localserver_view.freq_interval,
sysschedules_localserver_view.freq_subday_type,
sysschedules_localserver_view.freq_subday_interval,
sysschedules_localserver_view.freq_relative_interval,
sysschedules_localserver_view.freq_recurrence_factor,
sysschedules_localserver_view.active_start_date,
sysschedules_localserver_view.active_end_date,
substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sysschedules_localserver_view.active_start_time), 6), 1, 2)
+ ':'
+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), sysschedules_localserver_view.active_start_time), 6) ,3 ,2)
+ ':'
+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sysschedules_localserver_view.active_start_time), 6) ,5 ,2),
substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sysschedules_localserver_view.active_end_time), 6), 1, 2)
+ ':'
+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), sysschedules_localserver_view.active_end_time), 6) ,3 ,2)
+ ':'
+ substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sysschedules_localserver_view.active_end_time), 6) ,5 ,2)
FROM msdb.dbo.sysjobs_view
INNER JOIN msdb.dbo.sysjobschedules on sysjobs_view.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules_localserver_view on sysjobschedules.schedule_id = sysschedules_localserver_view.schedule_id
open curJob
fetch next from curJob into @job_name, @job_enabled, @schedule_enabled, @freq_type, @freq_interval,
@freq_subday_type, @freq_subday_interval, @freq_relative_interval, @freq_recurrence_factor,
@active_start_date, @active_end_date, @active_start_time, @active_end_time
while @@fetch_status = 0 begin
IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, @active_start_time)
-- RETURN
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
--RETURN
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
--RETURN
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, @active_start_time)
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
BEGIN
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, @active_start_time) + N' and ' + CONVERT(NVARCHAR, @active_end_time)
END
insert into @job_info (job_name, job_enabled, schedule_enabled, schedule_description)
values (@job_name, @job_enabled, @schedule_enabled, @schedule_description)
fetch next from curJob into @job_name, @job_enabled, @schedule_enabled, @freq_type, @freq_interval,
@freq_subday_type, @freq_subday_interval, @freq_relative_interval, @freq_recurrence_factor,
@active_start_date, @active_end_date, @active_start_time, @active_end_time
end
close curJob
deallocate curJob
select *
from @job_info
order by job_name[/font]
May 30, 2008 at 8:52 am
Although not very well documented 😉 you can launch "Job Activity Monitor", highlight the grid, then Ctrl+C (Copy). Then on Excel do Ctrl+V (Paste)
Cheers,
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply