Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Scheduled Jobs

DZone's Guide to

SQL Scheduled Jobs

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.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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 a monthly basis.

Straight to the Point

We will straight away take a look at the snippet and then try to understand 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 existance of the Local categories. What this does is simple.

From MSDN

Image title

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' }
  • @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 they are 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 to the MSDN for more info and the parameters definition.

Conclusion

That's it folks. We have seen the simple query that once run, creates an SQL job for us that can actually execute any query on the background, making life easy.

Points of Interest

We have followed TRANSACTION in the queries in order to track the errors and if any were 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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
sql ,jobs ,sql server

Published at DZone with permission of Suraj Sahoo, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}