Procedure to Create a New Job in SQL Server

Introduction

Before reading this article, I will recommend you to read a detailed article about Jobs in SQL Server. In this step-by-step tutorial, we will create a job in SQL Server, and how to execute it.

Step 1. Open SQL SERVER Agent then selects "Job" then right-click and select the "New Job" tab.

Step 2. It will open a new window, and provide a name (I used a new job).

Step 3. Go to "Steps"

Step 4. Click on the "New" button, it will open a new window.

Step 5. Specify a name.

Step 6. Write a query on the Stored Procedure (with exec spname syntax) in the command box.

Step 7. Click on "OK".

Step 8. Go to the "Schedule" Tab.

Step 9. Click on "New", that will open a new window.

Step 10. Specify a name and other settings.

Step 11. Schedule the job as your need.

Step 12. Click on "OK", this window will close.

Step 13. You can set an alert and notification from the other tab if you need to. If not then click on "OK".

Step 14. The new job will be shown in the job (folder) list.

In the above steps, we saw how to create and schedule a new job in SQL Server.

Create and Schedule a Job using a Query in SQL Server

The above steps and work can also be done using a SQL query without using the designer/wizard. Here is the complete query.

USE [msdb]  
GO   
/****** Object:  Job [new job]    Script Date: 05/03/2013 11:13:25 ******/  
BEGIN TRANSACTION  
DECLARE @ReturnCode INT  
SELECT @ReturnCode = 0  
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/03/2013 11:13:25 ******/  
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)  
BEGIN  
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
END   
DECLARE @jobId BINARY(16)  
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'new job',   
            @enabled=1,   
            @notify_level_eventlog=0,   
            @notify_level_email=0,   
            @notify_level_netsend=0,   
            @notify_level_page=0,   
            @delete_level=0,   
            @description=N'No description available.',   
            @category_name=N'[Uncategorized (Local)]',   
            @owner_login_name=N'INVEERA\Admin', @job_id = @jobId OUTPUT  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
/****** Object:  Step [step 1]    Script Date: 05/03/2013 11:13:26 ******/  
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1',   
            @step_id=1,   
            @cmdexec_success_code=0,   
            @on_success_action=1,   
            @on_success_step_id=0,   
            @on_fail_action=2,   
            @on_fail_step_id=0,   
            @retry_attempts=0,   
            @retry_interval=0,   
            @os_run_priority=0, @subsystem=N'TSQL',   
            @command=N'select * from table1',   
            @database_name=N'master',   
            @flags=0  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run',   
            @enabled=1,   
            @freq_type=4,   
            @freq_interval=1,   
            @freq_subday_type=4,   
            @freq_subday_interval=1,   
            @freq_relative_interval=0,   
            @freq_recurrence_factor=0,   
            @active_start_date=20130503,   
            @active_end_date=99991231,   
            @active_start_time=0,   
            @active_end_time=235959,   
            @schedule_uid=N'8f4ea511-099c-4502-890b-3d5673f36b21'  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
COMMIT TRANSACTION  
GOTO EndSave  
QuitWithRollback:  
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
EndSave:  
GO

Conclusion

This article taught us how we can create a new job task in SQL Server. Find a detailed article about jobs in SQL Server: Jobs in SQL Server.