Overview of SQL Jobs

Introduction

Scheduled jobs are a part of development, no matter the application. Inventory applications require 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 requirements. 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 a monthly basis.

Straight to the point

We will straight away check and see the snippet and then try to 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

The snippet part Line No. 9-12 results the existence 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' }

Where,

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 it's the query that will actually be executeed at intervals and fulfil your requirements.
@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

That's 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 are caused, then we can 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