|
Normal 0 21
Declare @vJobName VarChar (512)
Set @vJobName = Null
-- Null = TODOS
Declare @vJobSchedule_Id Int,
@vJobSchedule_FreqType Int,
@vJobSchedule_FreqInterval Int,
@vJobSchedule_FreqSubDayType Int,
@vJobSchedule_FreqSubDayInterval Int,
@vJobSchedule_FreqRelativeInterval Int,
@vJobSchedule_FreqRecurrenceFactor Int,
@vJobSchedule_ActiveStartDate Int,
@vJobSchedule_ActiveEndDate Int,
@vJobSchedule_ActiveStartTime Int,
@vJobSchedule_ActiveEndTime Int,
@vJobSchedule_Desc NVarChar (255)
Create Table #Jobs (Job_Id UniqueIdentifier Null,
Job_Name SysName Null,
Job_Enabled TinyInt Null,
JobStep_Id Int Null,
JobStep_Name SysName Null,
JobStep_SubSystem NVarChar (40) Null,
JobStep_Command NVarChar (4000) Null,
JobStep_DBName SysName Null,
JobStep_OutputFile NVarChar (200) Null,
JobSchedule_NexRunDate Int Null,
JobSchedule_NexRunTime Int Null,
JobSchedule_Id Int Null,
JobSchedule_Name SysName Null,
JobSchedule_FreqType Int Null,
JobSchedule_FreqInterval Int Null,
JobSchedule_FreqSubDayType Int Null,
JobSchedule_FreqSubDayInterval Int Null,
JobSchedule_FreqRelativeInterval Int Null,
JobSchedule_FreqRecurrenceFactor Int Null,
JobSchedule_ActiveStartDate Int Null,
JobSchedule_ActiveEndDate Int Null,
JobSchedule_ActiveStartTime Int Null,
JobSchedule_ActiveEndTime Int Null,
JobSchedule_Desc NVarChar (255) Null,
Procesado Bit Null
)
Insert Into #Jobs (Job_Id , Job_Name , Job_Enabled ,
JobStep_Id , JobStep_Name , JobStep_SubSystem ,
JobStep_Command , JobStep_DBName , JobStep_OutputFile ,
JobSchedule_NexRunDate , JobSchedule_NexRunTime , JobSchedule_Id ,
JobSchedule_Name , JobSchedule_FreqType , JobSchedule_FreqInterval ,
JobSchedule_FreqSubDayType , JobSchedule_FreqSubDayInterval , JobSchedule_FreqRelativeInterval,
JobSchedule_FreqRecurrenceFactor, JobSchedule_ActiveStartDate , JobSchedule_ActiveEndDate ,
JobSchedule_ActiveStartTime , JobSchedule_ActiveEndTime , Procesado
)
Select j.Job_id , j.Name , j.Enabled ,
js.Step_Id , js.Step_Name , js.SubSystem ,
js.Command , js.DataBase_Name , js.Output_File_Name ,
jt.Next_Run_Date , jt.Next_Run_Time , s.Schedule_Id ,
s.name , 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 , 0
From msdb..sysJobs As j
Inner Join msdb..sysJobSteps As js
On (j.Job_Id = js.Job_Id)
Inner Join msdb..sysJobSchedules As jt
Inner Join msdb..sysSchedules As s
On (jt.Schedule_id = s.Schedule_id)
On (j.Job_Id = jt.Job_Id)
Where ((j.Name Like '%' + @vJobName + '%') Or (@vJobName Is Null))
Select Top 1
@vJobSchedule_Id = JobSchedule_id ,
@vJobSchedule_FreqType = JobSchedule_FreqType ,
@vJobSchedule_FreqInterval = JobSchedule_FreqInterval ,
@vJobSchedule_FreqSubDayType = JobSchedule_FreqSubDayType ,
@vJobSchedule_FreqSubDayInterval = JobSchedule_FreqSubDayInterval ,
@vJobSchedule_FreqRelativeInterval = JobSchedule_FreqRelativeInterval,
@vJobSchedule_FreqRecurrenceFactor = JobSchedule_FreqRecurrenceFactor,
@vJobSchedule_ActiveStartDate = JobSchedule_ActiveStartDate ,
@vJobSchedule_ActiveEndDate = JobSchedule_ActiveEndDate ,
@vJobSchedule_ActiveStartTime = JobSchedule_ActiveStartTime ,
@vJobSchedule_ActiveEndTime = JobSchedule_ActiveEndTime
From #Jobs
Where Procesado = 0
While @@RowCount > 0
Begin
Exec msdb..SP_Get_Schedule_Description @vJobSchedule_FreqType ,
@vJobSchedule_FreqInterval ,
@vJobSchedule_FreqSubDayType ,
@vJobSchedule_FreqSubDayInterval ,
@vJobSchedule_FreqRelativeInterval,
@vJobSchedule_FreqRecurrenceFactor,
@vJobSchedule_ActiveStartDate ,
@vJobSchedule_ActiveEndDate ,
@vJobSchedule_ActiveStartTime ,
@vJobSchedule_ActiveEndTime ,
@vJobSchedule_Desc OUTPUT
Update #Jobs Set JobSchedule_Desc = @vJobSchedule_Desc,
Procesado = 1
Where JobSchedule_id = @vJobSchedule_Id
Select Top 1
@vJobSchedule_Id = JobSchedule_id ,
@vJobSchedule_FreqType = JobSchedule_FreqType ,
@vJobSchedule_FreqInterval = JobSchedule_FreqInterval ,
@vJobSchedule_FreqSubDayType = JobSchedule_FreqSubDayType ,
@vJobSchedule_FreqSubDayInterval = JobSchedule_FreqSubDayInterval ,
@vJobSchedule_FreqRelativeInterval = JobSchedule_FreqRelativeInterval,
@vJobSchedule_FreqRecurrenceFactor = JobSchedule_FreqRecurrenceFactor,
@vJobSchedule_ActiveStartDate = JobSchedule_ActiveStartDate ,
@vJobSchedule_ActiveEndDate = JobSchedule_ActiveEndDate ,
@vJobSchedule_ActiveStartTime = JobSchedule_ActiveStartTime ,
@vJobSchedule_ActiveEndTime = JobSchedule_ActiveEndTime
From #Jobs
Where Procesado = 0
End
Select Job_Name ,
Job_Enabled ,
JobStep_Id ,
JobStep_Name ,
JobStep_SubSystem ,
JobStep_Command ,
JobStep_DBName ,
JobStep_OutputFile ,
JobSchedule_NexRunDate ,
JobSchedule_NexRunTime ,
JobSchedule_Name ,
JobSchedule_Desc
From #Jobs
Order By Job_Name, JobSchedule_Id, JobStep_Id
Drop Table #Jobs
/* FIN */
|
