CODEWITHSUNDEEP
sqlsql-servert-sql
imdadhusen
imdadhusen

Reputation: 2494

Is it possible to create Job Schedule using T-SQL script (not SQL Server Agent schedule creation wizard)?

I have to create a script which will execute at scheduled times. Because my client is not familiar with SQL Server, I would like to create a schedule on my machine as per my client's requirements using SQL Server Agent schedule creation wizard, then create a script of the created schedule (which I will send to the client).

How can I create a T-SQL Job Schedule without using the SQL Server Agent schedule creation wizard?

Upvotes: 5

Views: 30433

Answers (5)

luc
luc

Reputation: 26

If you know the name of the job and want to add a new schedule, and then add this to multiple jobs you could script it this way (this is an example for a frequence of 1x/day at 02h00, added to 2 jobs)

USE [msdb]
GO

DECLARE @schedule_id int, @job_id uniqueidentifier

-- create the schedule
EXEC msdb.dbo.sp_add_schedule @schedule_name=N'name of the schedule', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20180703, 
        @active_end_date=99991231, 
        @active_start_time=020000, 
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- add it to job 1
set @job_id = (select job_id from msdb.dbo.sysjobs where name='Name of job 1')
EXEC msdb.dbo.sp_attach_schedule @job_id=@job_id,@schedule_id= @schedule_id

-- add it to job 2
set @job_id = (select job_id from msdb.dbo.sysjobs where name='Name of job 2')
EXEC msdb.dbo.sp_attach_schedule @job_id=@job_id,@schedule_id= @schedule_id

GO

Upvotes: 0

AlexandruC
AlexandruC

Reputation: 3637

To create a SQL Server Agent job using Transact-SQL:

  • Execute sp_add_job to create a job.
  • Execute sp_add_jobstep to create one or more job steps.
  • Execute sp_add_schedule to create a schedule.
  • Execute sp_attach_schedule to attach a schedule to the job.
  • Execute sp_add_jobserver to set the server for the job.

More here.

Upvotes: 8

Eric Higgins
Eric Higgins

Reputation: 700

I see you accepted the above answer, but if you're trying to create a schedule specifically which is what your question said, as opposed to a job, you use sp_add_schedule.

Upvotes: 3

niktrs
niktrs

Reputation: 10066

You can use management studio to get the creation script.

Create the job in management studio

Then right click the job and from the floating menu select Script The Job As -> Create To -> New Query Editor Window.

Alter the script details to match your needs

Upvotes: 11

davek
davek

Reputation: 22915

You can start SQL Profiler, run your wizard steps, and then see what SQL has been executed in the background, using this as a basis for preparing your scripts.

Upvotes: 1

Related Questions