use msdb
go
declare @beginday varchar(10)
declare @begintime varchar(9)
declare @endday varchar(10)
declare @endtime varchar(9)
declare @needtoRun datetime
declare @dayrun_sub int
declare @Begindate_sub varchar(20)
declare @Enddate_sub varchar(20)
declare @Senddate varchar(20)
declare @Senddate_sub varchar(20)
declare @Senddate_sub_sub varchar(20)
declare @weekth int
declare @dayrun_sub_sub int
declare @disableRep bit
declare @Begindate varchar(20)
declare @Enddate varchar(20)
--输出需要查询的运行时间范围(请尽量不要写入超过一天的范围，这样将会输出大量记录)
set @beginday='2012-03-31'
set @begintime=' 00:18:00'
set @endday='2012-04-01'
set @endtime=' 12:00:00'
--是否屏蔽replication相关job(0为不屏蔽，1为屏蔽)
set @disableRep=1
set @Begindate=@beginday+@begintime
set @Enddate=@endday+@endtime
declare @freq_type int
declare @freq_interval int
declare @job_name varchar(128)
declare @step_id int
declare @schedule_id int
declare @JobBegindate varchar(19)
declare @start_time varchar(9)
declare @freq_recurrence_factor int
declare @freq_relative_interval int
declare @attime varchar(5)
declare @Daily_every_times int
declare @Daily_every_type varchar(1)
declare @Daily_begintime varchar(5)
declare @Daily_endtime varchar(5)
DECLARE @i int
DECLARE @j int
DECLARE @n int
DECLARE @f int
declare @th int
if (object_id('tempdb..#tableJobDESC') is not null )
drop table #tableJobDESC
create table #tableJobDESC (
id int identity(1,1) primary key,
job_name varchar(128),
step_id int,
step_name sysname,
step_type Nvarchar(40),
schedule_id int,
schedules_name nvarchar(128),
command nvarchar(max),
Begindate varchar(19),
freq_type int,
freq_interval int ,
start_time varchar(9),
freq_recurrence_factor int,
freq_relative_interval int,
freq_subday_type int,
attime varchar(5),
Daily_every_times int,
Daily_every_type varchar(1),
Daily_begintime varchar(5),
Daily_endtime varchar(5),
DailyFrequency varchar(200)
)
CREATE NONCLUSTERED INDEX IX_#tableJobDESC_job_name_step_id_schedule_id ON #tableJobDESC
(
job_name,step_id,schedule_id
)WITH (FILLFACTOR = 90)
insert into #tableJobDESC
select agent_name,step_id,step_name,step_type,schedule_id,schedules_name,command,Begindate,freq_type,freq_interval,start_time,freq_recurrence_factor,
freq_relative_interval,freq_subday_type,attime,Daily_every_times,Daily_every_type,Daily_begintime,Daily_endtime,DailyFrequency
from
(
select
S.schedule_id,s.name as schedules_name,
(case when s.active_start_date=0 then '' else stuff(stuff(right('00000000'+ltrim(str(s.active_start_date)),8),5,0,'-'),8,0,'-') end)+' '+ stuff(stuff(right('000000'+ltrim(str(s.active_start_time)),6),3,0,':'),6,0,':') as Begindate,
(case when s.active_end_date=99991231 or s.active_end_time=235959 then '2100-01-01 00:00:00' else stuff(stuff(right('00000000'+ltrim(str(s.active_end_date)),8),5,0,'-'),8,0,'-') +' '+ stuff(stuff(right('000000'+ltrim(str(s.active_end_time)),6),3,0,':'),6,0,':') end) as Enddate,
s.freq_recurrence_factor,s.freq_relative_interval,j.job_id,J.name agent_name,P.step_id,P.step_name,
p.SubSystem step_type,P.command,s.freq_subday_type,s.freq_interval, s.freq_type,
stuff(stuff(right('000000'+ltrim(str(s.active_start_time)),6),3,0,':'),6,0,':') start_time
,CASE
WHEN j.enabled = 0 THEN 'Disabled'
WHEN j.job_id IS NULL THEN 'Unscheduled'
WHEN s.freq_type = 0x1 -- OneTime
THEN
'Once on '
+ CONVERT(
CHAR(10)
, CAST( CAST( s.active_start_date AS VARCHAR ) AS DATETIME )
, 102 -- yyyy.mm.dd
)
WHEN s.freq_type = 0x4 -- Daily
THEN 'Daily'
WHEN s.freq_type = 0x8 -- weekly
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' weeks on '
END
+ LEFT(
CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
, LEN(
CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1 -- LEN() ignores trailing spaces
)
WHEN s.freq_type = 0x10 -- monthly
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CAST( s.freq_interval AS VARCHAR )
+ CASE
WHEN s.freq_interval IN ( 1, 21, 31 ) THEN 'st'
WHEN s.freq_interval IN ( 2, 22 ) THEN 'nd'
WHEN s.freq_interval IN ( 3, 23 ) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20 -- monthly relative
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.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 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN 'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN 'Starts whenever the CPUs become idle'
ELSE ''
END
+ CASE
WHEN j.enabled = 0 THEN ''
WHEN j.job_id IS NULL THEN ''
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN ' at '
+ Case -- Depends on time being integer to drop right-side digits
when(s.active_start_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ convert(char(2),(s.active_start_time % 10000)/100)
+ ' AM'
when (s.active_start_time % 1000000)/10000< 10 then
convert(char(1),(s.active_start_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ convert(char(2),(s.active_start_time % 10000)/100)
+ ' AM'
when (s.active_start_time % 1000000)/10000 < 12 then
convert(char(2),(s.active_start_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ convert(char(2),(s.active_start_time % 10000)/100)
+ ' AM'
when (s.active_start_time % 1000000)/10000< 22 then
convert(char(1),((s.active_start_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ convert(char(2),(s.active_start_time % 10000)/100)
+ ' PM'
else convert(char(2),((s.active_start_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ convert(char(2),(s.active_start_time % 10000)/100)
+ ' PM'
end
WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN ' every '
+ CAST( s.freq_subday_interval AS VARCHAR )
+ CASE s.freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE
WHEN s.freq_subday_interval > 1 THEN 's'
ELSE '' -- Added default 3/21/08; John Arnott
END
ELSE ''
END
+ CASE
WHEN j.enabled = 0 THEN ''
WHEN j.job_id IS NULL THEN ''
WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN ' between '
+ Case -- Depends on time being integer to drop right-side digits
when(s.active_start_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_start_time % 10000)/100))
+ ' AM'
when (s.active_start_time % 1000000)/10000< 10 then
convert(char(1),(s.active_start_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_start_time % 10000)/100))
+ ' AM'
when (s.active_start_time % 1000000)/10000 < 12 then
convert(char(2),(s.active_start_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_start_time % 10000)/100))
+ ' AM'
when (s.active_start_time % 1000000)/10000< 22 then
convert(char(1),((s.active_start_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_start_time % 10000)/100))
+ ' PM'
else convert(char(2),((s.active_start_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_start_time % 10000)/100))
+ ' PM'
end
+ ' and '
+ Case -- Depends on time being integer to drop right-side digits
when(s.active_end_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_end_time % 10000)/100))
+ ' AM'
when (s.active_end_time % 1000000)/10000< 10 then
convert(char(1),(s.active_end_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_end_time % 10000)/100))
+ ' AM'
when (s.active_end_time % 1000000)/10000 < 12 then
convert(char(2),(s.active_end_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_end_time % 10000)/100))
+ ' AM'
when (s.active_end_time % 1000000)/10000< 22 then
convert(char(1),((s.active_end_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_end_time % 10000)/100))
+ ' PM'
else convert(char(2),((s.active_end_time % 1000000)/10000) - 12)
+ ':'
+Replicate('0',2 - len(convert(char(2),(s.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(s.active_end_time % 10000)/100))
+ ' PM'
end
ELSE ''
END as DailyFrequency,
CASE
WHEN J.enabled = 0 THEN ''
WHEN J.job_id IS NULL THEN ''
WHEN S.freq_subday_type = 0x1 OR S.freq_type = 0x1
then
Case -- Depends on time being integer to drop right-side digits
when(S.active_start_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ convert(char(2),(S.active_start_time % 10000)/100)
when (S.active_start_time % 1000000)/10000< 10 then
right('00'+convert(varchar(2),(S.active_start_time % 1000000)/10000) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ convert(char(2),(S.active_start_time % 10000)/100)
when (S.active_start_time % 1000000)/10000 < 12 then
right('00'+convert(varchar(2),(S.active_start_time % 1000000)/10000) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ convert(char(2),(S.active_start_time % 10000)/100)
when (S.active_start_time % 1000000)/10000< 22 then
right('00'+convert(varchar(2),((S.active_start_time % 1000000)/10000) ) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ convert(char(2),(S.active_start_time % 10000)/100)
else right('00'+convert(varchar(2),((S.active_start_time % 1000000)/10000)),2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ convert(char(2),(S.active_start_time % 10000)/100)
end
ELSE '' end
attime
,
case WHEN S.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN CAST( S.freq_subday_interval AS VARCHAR )
ELSE '' end as Daily_every_times
,
case WHEN S.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN CASE s.freq_subday_type
WHEN 0x2 THEN 's'
WHEN 0x4 THEN 'm'
WHEN 0x8 THEN 'h'
END
ELSE '' end as Daily_every_type
,
CASE
WHEN J.enabled = 0 THEN ''
WHEN J.job_id IS NULL THEN ''
WHEN S.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN
Case -- Depends on time being integer to drop right-side digits
when(S.active_start_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_start_time % 10000)/100))
when (S.active_start_time % 1000000)/10000< 10 then
right('00'+convert(varchar(2),(S.active_start_time % 1000000)/10000) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_start_time % 10000)/100))
when (S.active_start_time % 1000000)/10000 < 12 then
right('00'+convert(varchar(2),(S.active_start_time % 1000000)/10000) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_start_time % 10000)/100))
when (S.active_start_time % 1000000)/10000< 22 then
right('00'+convert(varchar(2),((S.active_start_time % 1000000)/10000) ) ,2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_start_time % 10000)/100))
else right('00'+convert(varchar(2),((S.active_start_time % 1000000)/10000) ),2)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_start_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_start_time % 10000)/100))
end
else ''
end
Daily_begintime
,
CASE
WHEN J.enabled = 0 THEN ''
WHEN J.job_id IS NULL THEN ''
WHEN S.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN Case -- Depends on time being integer to drop right-side digits
when(S.active_end_time % 1000000)/10000 = 0 then
'12'
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_end_time % 10000)/100))
when (S.active_end_time % 1000000)/10000< 10 then
convert(char(2),(S.active_end_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_end_time % 10000)/100))
when (S.active_end_time % 1000000)/10000 < 12 then
convert(char(2),(S.active_end_time % 1000000)/10000)
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_end_time % 10000)/100))
when (S.active_end_time % 1000000)/10000< 22 then
convert(char(2),((S.active_end_time % 1000000)/10000) )
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_end_time % 10000)/100))
else convert(char(2),((S.active_end_time % 1000000)/10000) )
+ ':'
+Replicate('0',2 - len(convert(char(2),(S.active_end_time % 10000)/100)))
+ rtrim(convert(char(2),(S.active_end_time % 10000)/100))
end
else ''
end
as Daily_endtime
from dbo.sysschedules S with(nolock)
inner join dbo.sysjobschedules SCH with(nolock) on SCH.schedule_id=S.schedule_id
inner join dbo.sysjobs J with(nolock) on SCH.job_id = J.job_id
inner join dbo.sysjobsteps P with(nolock) on SCH.job_id = P.job_id
where J.enabled=1
) as TB
where (Begindate <@Begindate and Enddate>@Begindate ) or (Begindate <@Enddate and Enddate>@Enddate )
if (object_id('tempdb..#tableJobRun') is not null )
drop table #tableJobRun
create table #tableJobRun (
id int identity(1,1) primary key,
job_name varchar(128),
step_id int,
schedule_id int,
Runtime datetime
)
CREATE NONCLUSTERED INDEX IX_#tableJobRun_job_name_step_id_schedule_id ON #tableJobRun
(
job_name,step_id,schedule_id
)WITH (FILLFACTOR = 90)
if @disableRep=1
DECLARE col_cur CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT freq_type,job_name,step_id,schedule_id,Begindate,freq_interval,start_time,freq_recurrence_factor,freq_relative_interval,
attime,Daily_every_times,Daily_every_type,Daily_begintime,Daily_endtime
FROM #tableJobDESC
where job_name in
(
SELECT name
FROM dbo.sysjobs with(nolock)
where category_id not in
(SELECT category_id
FROM dbo.syscategories with(nolock)
where (name like 'REPL-%' or name='Replication'))
) --屏蔽已分钟为单位运行的job and dailyfrequency not like '%minute%'
else
DECLARE col_cur CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT freq_type,job_name,step_id,schedule_id,Begindate,freq_interval,start_time,freq_recurrence_factor,freq_relative_interval,
attime,Daily_every_times,Daily_every_type,Daily_begintime,Daily_endtime
FROM #tableJobDESC
OPEN col_cur
FETCH NEXT FROM col_cur INTO @freq_type,@job_name,@step_id,@schedule_id,@JobBegindate,@freq_interval,@start_time,@freq_recurrence_factor,@freq_relative_interval,
@attime,@Daily_every_times,@Daily_every_type,@Daily_begintime,@Daily_endtime
--遍历所有job
WHILE @@FETCH_STATUS = 0
BEGIN
declare @interval int
declare @dayrun int
declare @daycount int
declare @wk int
declare @JobBeginday varchar(10)
declare @JobBegintime varchar(10)
set @JobBeginday=convert(varchar(10),@JobBegindate,120)
set @JobBegintime=convert(varchar(10),@JobBegindate,108)
declare @basedatetime varchar(20)
--只运行一次的job
if (@freq_type=1)
begin
set @needtoRun=@JobBeginday+' '+@attime
if (
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
--每天运行 (@freq_interval=每 freq_interval 天)
if (@freq_type=4)
begin
set @basedatetime=@JobBeginday+' '+@start_time
--当存在间隔天数时候，递增基数为@freq_interval
if @freq_interval=0
set @interval=1
else
set @interval=@freq_interval
set @dayrun= DATEDIFF(DAY ,@JobBeginday,@enddate)
set @i=0
while (@i<=@dayrun)
begin
set @needtoRun=dateadd(day,@interval*@i,@JobBeginday)
--若当天运行时间，在@Begindate和@enddate之间的
if (@needtoRun between @Beginday and @endday)
begin
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @needtoRun=@needtoRun+' '+@Daily_begintime
set @Senddate_sub=@needtoRun
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@needtoRun,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@needtoRun,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@needtoRun,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
set @i=@i+1
end
end
--每周运行的job
if (@freq_type=8)
begin
set @dayrun= DATEDIFF(DAY ,@JobBegindate,@enddate)
set @wk= DATEPART(dw,@JobBegindate)
--获得周期内有多少个week
set @dayrun_sub_sub=DATEDIFF(WEEK,@JobBegindate,@enddate)
set @i=0
set @weekth=@freq_recurrence_factor
--获得计算起点日期
set @Senddate=@JobBeginday
set @Senddate_sub=@JobBeginday
while (@i<=@dayrun_sub_sub)
begin
--间隔运行周数
if (((@i+1)%@weekth=1 and @weekth>1) or @weekth=1)
begin
--星期天
if (@freq_interval&1 = 1 )
begin
set @needtoRun=dateadd(day,1-@wk+@i*7,@Senddate_sub)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期一
if (@freq_interval&2= 2 )
begin
set @needtoRun=dateadd(day,2-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
--#region
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
--#endregion
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期二
if (@freq_interval&4 = 4 )
begin
set @needtoRun=dateadd(day,3-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
--#region
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--#endregion
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期三
if (@freq_interval&8 = 8 )
begin
set @needtoRun=dateadd(day,4-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期四
if (@freq_interval&16 = 16 )
begin
set @needtoRun=dateadd(day,5-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期五
if (@freq_interval&32 = 32 )
begin
set @needtoRun=dateadd(day,6-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
--星期六
if (@freq_interval&64 = 64 )
begin
set @needtoRun=dateadd(day,7-@wk+@i*7,@Senddate)
--若freq_subday_type不为1则是在一个范围内按固定频率多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
--若freq_subday_type为1则是在一个范围内按固定频率多次运行
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
end
set @i=@i+1
end
end
--每月运行的job
if (@freq_type=16)
begin
--当存在间隔月时候，@freq_recurrence_factor为具体间隔月数
if @freq_interval=0
set @interval=1
else
set @interval=@freq_recurrence_factor
--获得job运行开始那年那月那@JobBegindate+@freq_interval(具体几号运行)+@attime 作为计算基数
set @basedatetime=cast(DATEPART(YEAR,@JobBegindate) as varchar(4))+'-'+right('00'+cast(DATEPART(MONTH,@JobBegindate) as varchar(2)),2)+
'-'+right('00'+cast(@freq_interval as varchar(2)),2)
set @dayrun= DATEDIFF(MONTH,@basedatetime,@enddate)
set @i=0
--若计算基数日期，小于job开始运行日期，则不输出第一月
if (@basedatetime<@JobBegindate)
set @i=1
while (@i<=@dayrun)
begin
--间隔月数
if (((@i+1)%@interval=1 and @interval>1) or @interval=1)
begin
--若当周运行时间，在@Begindate和@enddate之间的
set @needtoRun=dateadd(MONTH,@i,@basedatetime)
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
--范围时间内，每间隔多少分钟运行
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
--范围时间内，每间隔多少小时运行
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
set @i=@i+1
end
end
--每月的job，包含当月第几周，第几工作日，间隔几个月设置的job
if (@freq_type=32)
begin
declare @tmepdatetime varchar(20)--临时记录当前月的第一天
--获得是第几个（1-4th or last）
set @th=(select (CASE @freq_relative_interval
WHEN 0x01 THEN 1
WHEN 0x02 THEN 2
WHEN 0x04 THEN 3
WHEN 0x08 THEN 4
WHEN 0x10 THEN 100
END)
)
--获得job运行开始那年那月那freq_day+starttime 作为计算基数
set @basedatetime=cast(DATEPART(YEAR,@JobBeginday) as varchar(4))+'-'+right('00'+cast(DATEPART(MONTH,@JobBeginday) as varchar(2)),2)+'-01'
--获得job开始到结束到底有多少个月
set @dayrun= DATEDIFF(MONTH,@basedatetime,@enddate)
set @i=0
while (@i<=@dayrun)
begin
--判断是否已经到需要运行的间隔月份
if (((@i+1)%@freq_recurrence_factor=1 and @freq_recurrence_factor>1) or @freq_recurrence_factor=1)
begin
--获得当前月判定月的第一天
set @tmepdatetime=dateadd(month,@i*@freq_recurrence_factor,@basedatetime)
----------------------------------------------------------------------------------
--判定是第几个星期几（1 = 星期日2 = 星期一3 = 星期二4 = 星期三5 = 星期四6 = 星期五7 = 星期六8 = 日9 = 工作日10 = 休息日）
if (@freq_interval>=1 and @freq_interval<=7)
begin
--得到当月第一天是星期几
set @wk= DATEPART(dw,@tmepdatetime)
set @interval=@freq_interval
set @interval=@interval-@wk
--获得当前月包含周数
set @dayrun_sub=DATEDIFF(DAY,@tmepdatetime,dateadd(day,-1,dateadd(month,1,@tmepdatetime)))
--循环获得当前第@j个星期，@freq_day
set @n =0
while (@n<=@dayrun_sub)
begin
set @needtoRun=dateadd(DAY ,@interval+@n*7,@tmepdatetime)
--若当周运行时间，在@Begindate和@enddate之间的,并且在当前判定月,判断是对应的个数
if (
@needtoRun between @Begindate and @enddate
and
@needtoRun between @tmepdatetime and dateadd(day,-1,dateadd(month,1,@tmepdatetime))
and
@n=@th-1--是否是目的周
)
begin
--判断是否是在当天固定范围内多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
set @n=@n+1
end
--若当前月仅4周，或存在第6周（对last的特殊处理）
if(@th=100)
begin
set @needtoRun=dateadd(DAY ,@interval+@dayrun_sub*7,@tmepdatetime)
if (
@needtoRun between @Begindate and @enddate
and
@needtoRun between @tmepdatetime and dateadd(day,-1,dateadd(month,1,@tmepdatetime))
)
begin
--判断是否是在当天固定范围内多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
end
end
----------------------------------------------------------------------------------
--8 = 日
if (@freq_interval=8)
begin
if (@th<100)
set @needtoRun=DATEADD(DAY,@th,@tmepdatetime)
else
set @needtoRun=DATEADD(DAY,-1,dateadd(month,1,@tmepdatetime))
--判断是否是在当天固定范围内多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
----------------------------------------------------------------------------------
--9 = 工作日10 = 休息日
if (@freq_interval=9 or @freq_interval=10)
begin
--当统计为工作日或休息日时
--判定当前星期数
set @wk= DATEPART(dw,@tmepdatetime)
--获得当前月包含周数
set @dayrun_sub=DATEDIFF(WEEK,@tmepdatetime,dateadd(day,-1,dateadd(month,1,@tmepdatetime)))
--循环获得当前第n个星期的星期几，
set @n=0
while (@n<=@dayrun_sub)
begin
--判定是否指定需要运行的第@th个周
--若当周运行时间，在@Begindate和@enddate之间的,并且在当前判定月,判断是对应的个数
if (@n=@th)--是否是目的,第n周 )
begin
set @f=0
--循环1周中的7天，对每天做一次是否运行的判定
while(@f<=7)
begin
if((@f between 2 and 6 and @freq_interval=10) or ((@f = 1 or @f=7) and @freq_interval=10))
begin
set @needtoRun=dateadd(DAY ,@n*7+(@f-@wk),@tmepdatetime)
--判断是否是在当天固定范围内多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
set @f=@f+1
end
end
set @n=@n+1
end
--若当前月仅4周，或存在第6周（对last的特殊处理）
if(@th=100)
begin
set @f=0
--循环1周中的7天，对每天做一次是否运行的判定
while(@f<=7)
begin
if((@f between 2 and 6 and @freq_interval=10) or ((@f = 1 or @f=7) and @freq_interval=10))
begin
set @needtoRun=dateadd(DAY ,@dayrun_sub*7+(@f-@wk),@tmepdatetime)
--判断是否是在当天固定范围内多次运行
if (@Daily_begintime!='')
begin
set @j=0
set @Begindate_sub=@needtoRun+' '+@Daily_begintime
set @Enddate_sub=@needtoRun+' '+@Daily_endtime
set @Senddate_sub=@needtoRun+' '+@Daily_begintime
--分不同间隔情况,分别输出当天运行期间内,发生的运行次数
--范围时间内，每间隔多少秒/分钟/小时运行
if (@Daily_every_type='s')
begin
set @dayrun_sub=DATEDIFF(SECOND ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(SECOND ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='m')
begin
set @dayrun_sub=DATEDIFF(MINUTE ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(MINUTE ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
if (@Daily_every_type='h')
begin
set @dayrun_sub=DATEDIFF(HOUR ,@Begindate_sub,@Enddate_sub)
while(@j<=@dayrun_sub)
begin
set @needtoRun=dateadd(HOUR ,@j,@Senddate_sub)
if (
(@needtoRun between @Begindate_sub and @Enddate_sub)
and
(@needtoRun between @Begindate and @enddate)
and
@needtoRun>=@JobBegindate
)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
set @j=@j+@Daily_every_times
end
end
end
else
begin
set @needtoRun=@needtoRun+' '+@attime
if (@needtoRun between @Begindate and @enddate and @needtoRun>=@JobBegindate)
begin
insert into #tableJobRun (job_name,step_id,schedule_id,Runtime)
select @job_name,@step_id,@schedule_id,@needtoRun
end
end
end
set @f=@f+1
end
end
end
----------------------------------------------------------------------------------
end
set @i=@i+1
end
end
----在 SQL Server 代理服务启动时运行（无法预知不考虑）
--if (@freq_type=64)
--begin
--end
----在计算机空闲时运行（无法预知不考虑）
--if (@freq_type=128)
--begin
--end
FETCH NEXT FROM col_cur INTO @freq_type,@job_name,@step_id,@schedule_id,@JobBegindate,@freq_interval,@start_time,@freq_recurrence_factor,@freq_relative_interval,
@attime,@Daily_every_times,@Daily_every_type,@Daily_begintime,@Daily_endtime
end
CLOSE col_cur
DEALLOCATE col_cur
select tb1.job_name,TB2.step_id,TB2.step_name,TB2.schedules_name,
case tb2.freq_type
when 1 then 'only one time'
when 4 then 'every day'
when 8 then 'every week'
when 16 then 'every month'
when 32 then '32'
when 64 then 'when SQLServerAgent server start'
when 128 then 'when system Idle time'
else '' end freq_type
,tb1.Runtime,tb2.DailyFrequency ,
TB2.step_type ,TB2.command from #tableJobRun AS TB1
INNER JOIN #tableJobDESC AS TB2
ON TB1.job_name =TB2.job_name and TB1.step_id =TB2.step_id and TB1.schedule_id =TB2.schedule_id
--where tb1.DailyFrequency not like 'Daily every 10 minutes%' and tb1.DailyFrequency not like 'Daily every 1 minutes%'
--and tb1.DailyFrequency not like 'Daily every 2 minutes%'and tb1.DailyFrequency not like 'Daily every 3 minutes%'
--and tb1.DailyFrequency not like 'Daily every 5 minutes%' and tb1.DailyFrequency not like 'Daily every 1 minute%'
--where tb1.job_name ='JOb_ACT.dbo.UP_PMT_JOB_GCTransferForOldRedeemSO'
order by job_name,step_id,schedules_name
--select * from #tableJobRun
--select * from #tableJobDESC
--if (object_id('tempdb..#tableJobDESC') is not null )
-- drop table #tableJobDESC
--if (object_id('tempdb..#tableJobRun') is not null )
-- drop table #tableJobRun
查询某一个时间段，是否会有JOB会运行
最新推荐文章于 2024-03-19 20:44:24 发布
扫一扫
641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
