SQL Query: Schedule multiple jobs to run over night

I had about 40 sql agent jobs. I had to schedule them to run one time only over night. It was a bit of hectic to open every job, change the ownership, modify the schedule time, then change the ownership back to the service account. In order to minimize the work effort, I have written a script based on CURSOR. Where I will have a control table, it will define the job name, schedule name, schedule start date, schedule start time. I will read this from the control table and the change the schedule accordingly.

 

USE msdb;USE msdb;go
/*create the control table and insert some dummy data.In order to make this script work, there has to be job exist defined in the control table.*/
CREATE TABLE [ETL].[SqlJobControl]( [SqlJobControlID] [int] IDENTITY(1,1) NOT NULL, [job_id] [int] NULL, [job_name] [nvarchar](200) NULL, [schedule_name] [nvarchar](50) NULL, [job_description] [nvarchar](100) NULL, [active_start_date] [int] NULL, [active_start_time] [int] NULL, [owner_login_name_user] [nvarchar](50) NULL, [owner_login_name_service_account] [nvarchar](50) NULL, [enabled] [bit] NULL, CONSTRAINT [PK__SqlJobCo__D32BB6AA03DE9823] PRIMARY KEY CLUSTERED ( [SqlJobControlID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GOSET IDENTITY_INSERT [ETL].[SqlJobControl] ON
INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (1, NULL, N’Developer_Test_Job1′, N’OneOff’, N’Dummy job’, 20170613, 175500, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (2, NULL, N’Developer_Test_Job2′, N’OneOff’, N’Dummy job’, 20170614, 180000, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)SET IDENTITY_INSERT [ETL].[SqlJobControl] OFF;
DECLARE  @job_id int, @job_name nvarchar(200), @schedule_name nvarchar(50), @job_description nvarchar(100), @active_start_date int, @active_start_time int, @owner_login_name_user nvarchar(50), @owner_login_name_service_account nvarchar(50), @enabled bit
;
DECLARE @oneoff_sqljob_cursor AS CURSOR;
SET @oneoff_sqljob_cursor = CURSOR FAST_FORWARD FOR SELECT  [job_id] ,[job_name] ,[schedule_name] ,[job_description] ,[active_start_date] ,[active_start_time] ,[owner_login_name_user] ,[owner_login_name_service_account] ,[enabled] FROM [UNIFY2].[ETL].[SqlJobControl] WHERE [enabled]=1;
OPEN @oneoff_sqljob_cursor;FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;
WHILE @@FETCH_STATUS = 0BEGIN EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_user; EXEC msdb.dbo.sp_detach_schedule  @job_name=@job_name,  @schedule_name=@schedule_name, @delete_unused_schedule=1 ;
EXEC msdb.dbo.sp_add_jobschedule  @job_name=@job_name,  @name=@schedule_name,  @enabled=1,  @freq_type=1,  @freq_interval=1,  @freq_subday_type=0,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=@active_start_date,  @active_start_time=@active_start_time ; EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_service_account; FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s