Join Class

Create SQL jobs in SQL server

What is Job in SQL Server?
A job in SQL server is basically a bunch of tasks (queries) performed sequentially by SQL Server Agent.

job in sql 2005

  • Open the SQL Server Management Studio on your local window and you should have the “SQL Server Agent” installed
  • Expand the "SQL Server Agent" in Object Explorer
  • Right click on the Jobs and select the "New Job"
  • You will see a new window, give a valid name for new job, and click "Ok" button.
  • At this point a new job has been created; you can refresh the explorer to see the new job, right click to see properties and set configuration details

Create sql job in sql server 2005, 2012

You also can create a job in sql server by running following query in sql query manager.

EXEC dbo.sp_add_job
   @job_name = N'Monthly Sales Report';
EXEC sp_add_jobstep
    @job_name = N'Monthly Sales Report',
    @step_name = N'Set database to read only',
    @subsystem = N'TSQL',
    @retry_attempts = 5,
    @retry_interval = 5;

There are some built-in stored procedure to create job, schedule job, attach and add job server.

  • sp_add_job (Transact-SQL)
  • sp_add_jobstep (Transact-SQL)
  • sp_add_schedule (Transact-SQL)
  • sp_attach_schedule (Transact-SQL)
  • sp_add_jobserver (Transact-SQL)

How to call a SQL JOB from Stored PROCEDURE

Normally SQL Jobs are created for automation, means things can be carried out without further human interaction, but there are situation where jobs needs to be executed on demand or in some conditional situation.

Here we create a simple store procedure to execute JOB

CREATE PROC mySpToExecutejob
	EXEC dbo.sp_start_job N'Monthly Newletter';

Now you can call the store procedure from SQL code or execute directly

EXEC mySpToExecutejob

Learn more about SQL Jobs using SQL Server management studio.

Hire SQL Developer
Jobs in SQL Server
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
MS SQL Examples | Join MS SQL Course