Personal WebSite of Leonardo Sasso

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 */