Procedure to Create a New Job in SQL Server 2008

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 seelct "Job" then right-click and select the "New Job" tab.

image1.gif

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

image2.gif

Step 3 : Go to "Steps"

image3.gif

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

image4.gif

Step 5 : Specify a name.

image5.gif

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

image6.gif

Step 7 : Click on "OK".

image7.gif

Step 8 : Go to the "Schedule" Tab.

image9.gif

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

image10.gif

Step 10 :
 Specify a name and other settings.

image11.gif

Step 11 : Schedule the job as your need.

image12.gif

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

image13.gif

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

image14.gif

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

image15.gif
 
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
 
The above steps and work can also be done using a SQL query without using the designer/wizard. Here is the complete query. 
  1. USE [msdb]  
  2. GO   
  3. /****** Object:  Job [new job]    Script Date: 05/03/2013 11:13:25 ******/  
  4. BEGIN TRANSACTION  
  5. DECLARE @ReturnCode INT  
  6. SELECT @ReturnCode = 0  
  7. /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/03/2013 11:13:25 ******/  
  8. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)  
  9. BEGIN  
  10. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'  
  11. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  12. END   
  13. DECLARE @jobId BINARY(16)  
  14. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'new job',   
  15.             @enabled=1,   
  16.             @notify_level_eventlog=0,   
  17.             @notify_level_email=0,   
  18.             @notify_level_netsend=0,   
  19.             @notify_level_page=0,   
  20.             @delete_level=0,   
  21.             @description=N'No description available.',   
  22.             @category_name=N'[Uncategorized (Local)]',   
  23.             @owner_login_name=N'INVEERA\Admin', @job_id = @jobId OUTPUT  
  24. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  25. /****** Object:  Step [step 1]    Script Date: 05/03/2013 11:13:26 ******/  
  26. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1',   
  27.             @step_id=1,   
  28.             @cmdexec_success_code=0,   
  29.             @on_success_action=1,   
  30.             @on_success_step_id=0,   
  31.             @on_fail_action=2,   
  32.             @on_fail_step_id=0,   
  33.             @retry_attempts=0,   
  34.             @retry_interval=0,   
  35.             @os_run_priority=0, @subsystem=N'TSQL',   
  36.             @command=N'select * from table1',   
  37.             @database_name=N'master',   
  38.             @flags=0  
  39. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  40. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1  
  41. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  42. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run',   
  43.             @enabled=1,   
  44.             @freq_type=4,   
  45.             @freq_interval=1,   
  46.             @freq_subday_type=4,   
  47.             @freq_subday_interval=1,   
  48.             @freq_relative_interval=0,   
  49.             @freq_recurrence_factor=0,   
  50.             @active_start_date=20130503,   
  51.             @active_end_date=99991231,   
  52.             @active_start_time=0,   
  53.             @active_end_time=235959,   
  54.             @schedule_uid=N'8f4ea511-099c-4502-890b-3d5673f36b21'  
  55. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  56. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'  
  57. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  
  58. COMMIT TRANSACTION  
  59. GOTO EndSave  
  60. QuitWithRollback:  
  61.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
  62. EndSave:  
  63. GO  
 
Here is a detailed tutorial: Jobs in SQL Server