Procedure to Create a Job Task in SQL Server 2008

Step 1 : Go To SQL SERVER Agent then seelct "Job" then right-click and select the "New Job" tab.



Step 2 : It will open a new window, provide a name (I used 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.



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.



Note : The step above can manage by the given query also, but it is a little complex for beginners.

USE [msdb]GO /****** Object: Job [new job] Script Date: 05/03/2013 11:13:25 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:
GO


Reference:

Nice Article by Pankaj Pandey

http://www.c-sharpcorner.com/UploadFile/0fca19/step-by-step-procedure-to-create-a-job-task-in-sql-server-20/