question

NaomiNNN avatar image
0 Votes"
NaomiNNN asked GuoxiongYuan-7218 commented

SQL Agent jobs schedules

Hi everybody,

I have about 30 SQL Server Agent jobs. I want every job (except for a few not ours) to have the schedule named SCHED_ + name of the job. Some jobs already have schedule named this way, some use (and share) the schedule named differently. In case they use and share a schedule I need to make a copy of the current schedule with the aforementioned name.

Is there a way to automate such process? I don't want to manually create new schedules matching original and unfortunately the 'Copy' button is missing when I look at the schedule for the job in its properties.

What can I do? [Going to start on the manual process for now while waiting for response]

Thanks in advance.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

Try this:

 USE [msdb];
 GO
    
 SELECT 'SCHED_' + j.name
 --UPDATE s
 --SET s.name = 'SCHED_' + j.name
   FROM [dbo].[sysjobschedules] AS js
 INNER JOIN [dbo].[sysschedules] AS s ON s.schedule_id = js.schedule_id
 INNER JOIN [dbo].[sysjobs] AS j ON j.job_id = js.job_id
 WHERE j.name NOT IN ('NOT_MY_JOB_01', 'NOT_MY_JOB_02') 
 AND s.name NOT LIKE 'SCHED_%'
 AND j.enabled = 1;


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot, going to try and probably use as insert? Need to find a way to create new schedule.

0 Votes 0 ·

If you want to create a new schedule, you may need to use [msdb].[dbo].[sp_add_jobschedule].

0 Votes 0 ·