Simple way to create a SQL Server Job Using T-SQL

By:   |   Updated: 2013-09-25   |   Comments (15)   |   Related: More > SQL Server Agent


Sometimes we have a T-SQL process that we need to run that takes some time to run or we want to run it during idle time on the server. We could create a SQL Agent job manually, but is there any simple way to create a scheduled job?


This tip contains T-SQL code to create a SQL Agent job dynamically instead of having to use the SSMS GUI.

I am going to create a stored procedure named sp_add_job_quick that takes a few parameters to create the job. For my example, I will create a SQL Agent job that will call stored procedure sp_who and the job will be scheduled to run once at 4:00 PM.

Creating a Stored Procedure to create SQL Agent jobs

In this sample we are going to create a job dynamically using T-SQL Code:

USE msdb
CREATE procedure [dbo].[sp_add_job_quick] 
@job nvarchar(128),
@mycommand nvarchar(max), 
@servername nvarchar(28),
@startdate nvarchar(8),
@starttime nvarchar(8)
--Add a job
EXEC dbo.sp_add_job
    @job_name = @job ;
--Add a job step named process step. This step runs the stored procedure
EXEC sp_add_jobstep
    @job_name = @job,
    @step_name = N'process step',
    @subsystem = N'TSQL',
    @command = @mycommand
--Schedule the job at a specified date and time
exec sp_add_jobschedule @job_name = @job,
@name = 'MySchedule',
@active_start_date = @startdate,
@active_start_time = @starttime
-- Add the job to the SQL Server Server
EXEC dbo.sp_add_jobserver
    @job_name =  @job,
    @server_name = @servername

This is a stored procedure named sp_add_job_quick that calls 4 msdb stored procedures:

  1. sp_add_job creates a new job
  2. sp_add_jobstep adds a new step in the job
  3. sp_add_jobschedule schedules a job for a specific date and time
  4. sp_add_jobserver adds the job to a specific server

Let's invoke the stored procedure in order to create the job:

exec dbo.sp_add_job_quick 
@job = 'myjob', -- The job name
@mycommand = 'sp_who', -- The T-SQL command to run in the step
@servername = 'serverName', -- SQL Server name. If running localy, you can use @[email protected]@Servername
@startdate = '20130829', -- The date August 29th, 2013
@starttime = '160000' -- The time, 16:00:00

If everything is OK, a job named myjob will be created with a step that runs the sp_who stored procedure that will run on August 29th at 4:00PM.

job created

Explanation of the SQL Agent job creation code

Here I will walk through the code and what each step does.

The sp_add_job is a procedure in the msdb database that creates a job.

EXEC dbo.sp_add_job
    @job_name = @job   

The sp_add_jobstep creates a job step in the job created. In this tip, the step name is process_step and the action is a TSQL command.

EXEC sp_add_jobstep
    @job_name = @job,
    @step_name = N'process step',
    @subsystem = N'TSQL',
    @command = @mycommand

job step

In the declare section we are assigning to the @mycommand variable the stored procedure sp_who.

job step stored procedure

The following section let's you create the schedule for the job in T-SQL. The schedule name is MySchedule. The frequency type is once (1). If you need to run the job daily the frequency type is 4 and weekly 8 . The active start time is 16:00:00 (4PM). The start date uses the date assigned to the startdate variable '20130823'.

exec sp_add_jobschedule @job_name = @job,
@name = 'MySchedule',
@active_start_date = @startdate,
@active_start_time = @starttime

sql job schedule

Additional tables to find information about SQL Agent jobs

Here are some system tables in the msdb database that you can use to get job information. If you need to retrieve job information you may need them.

  • dbo.sysjobactivity - shows the current information of the jobs
  • dbo.sysjobhistory - shows the execution result of the jobs.
  • dbo.sysjobs - shows the information of the jobs programmed.
  • dbo.sysjobsshedules - shows the job schedule information like the next run date and time of the jobs.
  • dbo.sysjobservers - shows the servers assigned to run jobs
  • dbo.sysjobsteps - shows the job steps
  • dbo.sysjobsteplogs - let you see the logs of the steps configured to display the output in a table

SQL Agent Job stored procedures

You also have these stored procedures in the msdb database to retrieve job information:

  • sp_helpjob
  • sp_helpjobactivity
  • sp_helpjobcount
  • sp_helpjobhistory
  • sp_helpjobhistory_full
  • sp_helpjobhistory_sem
  • sp_helpjobhistory_summary
  • sp_helpjobs_in_schedule
  • sp_help_jobschedule
  • sp_help_jobserver
  • sp_help_jobstep
  • sp_help_jobssteplog

I am not going to explain each system stored procedure, but in the next steps you can find links with an explanation for each.

If you want to modify and create your own procedures based on the Microsoft system stored procedures you can review the code using sp_helptext. In this example, we are reviewing sp_help_job. For example, to see the T-SQL code for sp_help_job code use this command:

sp_helptext '[dbo].[sp_help_job]'

The code is displayed here:

stored procedure code
Next Steps

For more information about creating jobs with T-SQL refer to these links:

Last Updated: 2013-09-25

get scripts

next tip button

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources

Comments For This Article

Friday, December 18, 2020 - 5:09:16 PM - Spencer A Sullivan Back To Top (87932)
Fantastic article and great explanations. Thank you!

Wednesday, May 23, 2018 - 9:49:56 AM - yetanis20 Back To Top (76010)

Excelente articulo! me ayudo bastante, execlente explicación paso a paso.

Tuesday, March 20, 2018 - 2:07:10 AM - Sheik Ahmed SM Back To Top (75473)


 Nice Article

Thursday, November 23, 2017 - 4:57:27 AM - Rajendra Back To Top (70120)


 Good article.

I need a article with updation of every five seconds time interval.

Tuesday, August 08, 2017 - 6:59:03 AM - c Back To Top (64298)




good one

Thursday, July 20, 2017 - 2:00:19 AM - Andy K Back To Top (59818)

 Thank you very much for this article.

Very concise and straightforward with good example.


Wednesday, July 20, 2016 - 12:38:35 PM - Farooqui Back To Top (41931)


 Nice Article, it really helped me.

Wednesday, March 04, 2015 - 5:43:58 AM - King Back To Top (36440)

good article

Friday, August 01, 2014 - 12:22:35 AM - Thankful User Back To Top (33967)

Thank you very much for this post! It REALLY helped me.

Thursday, July 24, 2014 - 8:35:59 AM - pooja Back To Top (32858)


i am trying to schedule a job in ql server 2005 for calling a stored procedure daily at a fixe time, at the time of adding the steps i am getting the message in window 'after creating the steps that after executing the steps  go to next step will goes to quit with success. is this intended behavoiur' . while clicking on yes it gives exception

   'execute permission deny for sp_help_operator,msdb '.and it does not create the steps in job. could you provide me the solution .

thanks in advance

Saturday, October 12, 2013 - 5:52:58 PM - Brahim Back To Top (27134)

This is a usefull article. Thanks a lot.

Tuesday, October 08, 2013 - 9:32:26 AM - Daniel Back To Top (27080)

The easiest way is to specify the database name in the command.

Tuesday, October 08, 2013 - 9:27:30 AM - cody leaf Back To Top (27079)

What is the parameter for the database setting if you don't want it to always be "MASTER"?



Wednesday, September 25, 2013 - 4:24:06 PM - Greg Robidoux Back To Top (26948)

Thanks for pointing that out. The tip has been updated to fix the typo you mentioned.

Wednesday, September 25, 2013 - 3:30:30 PM - TimothyAWiseman Back To Top (26947)

I was not aware of some of those system stored procedures, so this is very enlightening.  Thank you.

Just a small nitpick, but is " The active start time is 16:00:00 (00:00:00)." a typo?  Should it read (04:00:00) to translate the 24-hour clock to the more conventional 12-hour clock?  Or am I just misunderstanding something?


Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

Queries to inventory your SQL Server Agent Jobs

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables

get free sql tips
agree to terms