Sunday, December 30, 2018

TSQL to schedule multiple jobs in SQL server.

Hi Friends,

Sometime we need to schedule multiple jobs and at that time we have to do it manually by opening properties of each job and changing schedule time manually.

Today I am going to provide script using which you can configure end date of job and schedule multiple jobs. In this script you just need to provide common filter in all jobs which you want to schedule or using second script you can schedule required jobs by mentioning their names.

So find below scripts, use them and let me know feedback.

script 1 :-

declare @job_ids sysname
declare @schedule_ids sysname
declare @str varchar(4000)
declare @env varchar (1000)
set @env ='%china_jobs%'------------------------------------------------Mention common filter in all jobs here
declare @date varchar (500)
set @date ='20180412'--------------------------------------------------Mention date in YYYYMMDD format


declare  job_enable_cursor cursor for
select jn.job_id,js.schedule_id from sysjobs jn join sysjobschedules js on jn.job_id=js.job_id
where jn.name like @env and jn.enabled = 1

OPEN job_enable_cursor

FETCH NEXT FROM job_enable_cursor  
INTO @job_ids, @schedule_ids 
WHILE @@FETCH_STATUS = 0

begin

select @str = 'EXEC msdb.dbo.sp_attach_schedule @job_id=N'''+ @job_ids +''',@schedule_id='+@schedule_ids+
' EXEC msdb.dbo.sp_update_schedule @schedule_id='+@schedule_ids+', @enabled=1,@active_end_date='+@date

exec (@str)

FETCH NEXT FROM job_enable_cursor  
INTO @job_ids, @schedule_ids 


end
close job_enable_cursor
deallocate job_enable_cursor

go



Script 2 :-




declare @job_ids sysname
declare @schedule_ids sysname
declare @str varchar(4000)
declare @date varchar (500)
set @date ='20180412' -------------------------- Mention date in YYYYMMDD format

declare  job_enable_cursor cursor for
select jn.job_id,js.schedule_id from sysjobs jn join sysjobschedules js on jn.job_id=js.job_id
Where  jn.enabled = 1 and

 jn.name in  ('Job_1','Job_2')-----------------------------mention job names here



OPEN job_enable_cursor

FETCH NEXT FROM job_enable_cursor  
INTO @job_ids, @schedule_ids 
WHILE @@FETCH_STATUS = 0

begin

select @str = 'EXEC msdb.dbo.sp_attach_schedule @job_id=N'''+ @job_ids +''',@schedule_id='+@schedule_ids+
' EXEC msdb.dbo.sp_update_schedule @schedule_id='+@schedule_ids+', @enabled=1,@active_end_date=@date

exec (@str)

FETCH NEXT FROM job_enable_cursor  
INTO @job_ids, @schedule_ids 


end
close job_enable_cursor
deallocate job_enable_cursor

go

No comments:

Post a Comment