4.67 avg. rating (93% score) - 3 votes

Introduction

Scheduled jobs are a part of development, may it be any application. Inventory applications require them the most when they automate almost everything. 🙂 SQL Jobs can prove to be handy when a query needs to run and update or insert or anything of that sort at regular intervals as per requirement. These are similar to schedulers to track your reminders and updates.
According to MSDN

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server 2016.

SQL Jobs are run by the SQL Server Agents. It can be a single operation running in the background or a series of operations.The SQL Jobs can be added using the GUI provided by the SQL Server. But in this article, we will see and understand the Query that helps create SQL Jobs. These jobs can be scheduled to run daily, weekly or on monthly basis.

Straight to the point

We will straight away check and see the snippet and then try understand the parts of it.

BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
DECLARE @owner_Login_Id NVARCHAR(30), 
        @actual_database_name NVARCHAR(30);
SET @owner_Login_Id = N''; -- ADD THE LOGIN NAME TO THE SERVER
SET @actual_database_name = N'DB NAME'; -- ADD THE DATABASE NAME AS REQUIRED

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'JOB_NAME',
     @enabled = 1,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'JOB_DESCRIPTION',
     @category_name = N'[Uncategorized (Local)]',
     @owner_login_name = @owner_Login_Id,
     @job_id = @jobId OUTPUT;
IF( @@ERROR <> 0
 OR @ReturnCode <> 0
  )
GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,//-- ID of the Job created
     @step_name = N'STEP_NAME', //--Step Name for the JOB
     @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'YOUR QUERY GOES HERE',
     @database_name = @actual_database_name, //--Database name is generic and defined above at the start
     @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'JOB_SCHEDULE_NAME',
     @enabled = 1,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 1,
     @freq_subday_interval = 0,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20150615,
     @active_end_date = 99991231,
     @active_start_time = 0,
     @active_end_time = 235959,
     @schedule_uid = N'af82656c-e151-4ebb-9fe1-009495e1415d';
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

100

BEGIN TRANSACTION;

DECLARE @ReturnCode INT;

SELECT @ReturnCode = 0;

DECLARE @owner_Login_Id NVARCHAR(30),

@actual_database_name NVARCHAR(30);

SET @owner_Login_Id = N''; -- ADD THE LOGIN NAME TO THE SERVER

SET @actual_database_name = N'DB NAME'; -- ADD THE DATABASE NAME AS REQUIRED

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'JOB_NAME',

@enabled = 1,

@notify_level_eventlog = 0,

@notify_level_email = 0,

@notify_level_netsend = 0,

@notify_level_page = 0,

@delete_level = 0,

@description = N'JOB_DESCRIPTION',

@category_name = N'[Uncategorized (Local)]',

@owner_login_name = @owner_Login_Id,

@job_id = @jobId OUTPUT;

IF( @@ERROR <> 0

OR @ReturnCode <> 0

)

GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

@job_id = @jobId,//-- ID of the Job created

@step_name = N'STEP_NAME', //--Step Name for the JOB

@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'YOUR QUERY GOES HERE',

@database_name = @actual_database_name, //--Database name is generic and defined above at the start

@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'JOB_SCHEDULE_NAME',

@enabled = 1,

@freq_type = 4,

@freq_interval = 1,

@freq_subday_type = 1,

@freq_subday_interval = 0,

@freq_relative_interval = 0,

@freq_recurrence_factor = 0,

@active_start_date = 20150615,

@active_end_date = 99991231,

@active_start_time = 0,

@active_end_time = 235959,

@schedule_uid = N'af82656c-e151-4ebb-9fe1-009495e1415d';

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:

The snippet part Line No. 9-12 results the existance of the Local categories. What this does is simple.
From MSDN

Contains the categories used by SQL Server Management Studio to organize jobs

Now, after the check, if the category exists that governs the organization job, it then executes the pre-defined Stored procedure that adds a new category sp_add_category

sp_add_category 
     [ [ @class = ] 'class', ] 
     [ [ @type = ] 'type', ] 
     { [ @name = ] 'name' }

sp_add_category

[ [ @class = ] 'class', ]

[ [ @type = ] 'type', ]

{ [ @name = ] 'name' }

@class:Class governs the class i.e. may be JOB|ALERT|OPERATOR
@type: This specifies the type or location of the Job i.e. LOCAL|MULTI-SERVER|NONE
@name: Category Name that will be added

After this the Job Addition SP is executed. The parameters for the SP needs to be known and why are they added. For more information MSDN can be followed.
This SP sp_add_job simply and only adds the JOB with the specified name. For the execution of JOB and to let it execute the required process, we need to add a JOB STEP, which is created next.
sp_add_jobstep does the job for us. This SP adds the specific Step to be executed or run under that JOB. The parameters here can also be referred from MSDN.
@command is the most important parameter here, as the query that will actually be executeed at intervals and fulfil your requirement will run.
@database_name is also important as this would specify on which database the query would be executed by the JOB.
We are then adding the schedule for the created job using SP sp_add_jobschedule . This Sp adds the required schedule for the JOB and sets the JOB to run based on the schedule. Please refer the MSDN for more info and the parameters definition.

Conclusion

Thats it folks. We have seen the simple query that once run, creates a SQL job for us that can actually execute any query on the background and make life easy. 😛

Points of Interest

We have followed TRANSACTION in the queries in order to track the errors and if any caused, then we could roll back the changes to avoid ambiguity.
Instead of now following steps to create Jobs using the SQL Server Object Explorer, run the above query, understand the parameters once and then that’s easy.
Hope this helps.

References

MSDN