SQL Server agent, how to create SQL jobs using T-SQL?

All the big companies, like Discord, Facebook, Google, etc., store their data in databases. As these databases are being used by millions of users and contain billions of records, it is not possible for a human or group of humans to enter user data manually. We need to automate these tasks, and this is where we can use the SQL Server agent to create a SQL Server Agent job using T-SQL, which automates the tasks for data manipulation using the SQL Server procedures.

Introduction

The SQL Server Agent is a powerful tool that can automate tasks in SQL Server. It allows scheduled jobs to be created and executed in the background without user intervention. This allows for tasks such as backing up databases, implementing stored procedures, or sending out reports to be automated, freeing up resources and improving efficiency. Creating jobs in the SQL Server Agent can be done using the T-SQL language which we use to interact with SQL Server.

In this article, we’ll have a look at creating a SQL Server Agent job using T-SQL server procedures.

System stored procedures of the MSDB database

The following queries are used by the SQL server:-

Creating a Job using Sp_add_job Query

The first step in creating a SQL Server Agent job is to create a job object in SSMS. This is done using the sp_add_job system stored procedure. This procedure will create a job object but not execute any tasks, steps must be added to it

This stored procedure is used to create a new job in the SQL Server Agent. It takes the following arguments:

@job_name: This is the name of the job to be created.
@owner_login_name: This is the login name of the user who will own the job.
@description: This is an optional description of the new job.
@category_name: This is an optional name of the category to which the job belongs.
@start_step_id: This is an optional parameter that specifies the job step to be executed when the job is first started.
@subsystem: This is an optional parameter that specifies the subsystem to be used when running the job.
@command: This is an optional parameter that specifies the command to be run when the job is executed.
@server: This is an optional parameter that specifies the server on which the job should run.
@database_name: This is an optional parameter that specifies the database to be used when running the job.
@notify_level_eventlog: This is an optional parameter that specifies the level of event log entries to be written when the job runs.

Value	Definition
0	The job’s results won’t be recorded in the event log.
1	Results will be written to the event viewer if the job runs successfully.
2 [Default]	A result and error message will be written to the event viewer if the job fails.
3	The job’s results are recorded in the event viewer.

@notify_level_eventlog

@notify_level_email: This is an optional parameter that specifies the level of email notifications to be sent when the job runs.
@notify_level_netsend: This is an optional parameter that specifies the level of net send notifications to be sent when the job runs.

Sp_add_jobserver Query

The sp_add_jobserver stored procedure is used to define a job server for the SQL Server Agent job. It is used to specify a server to act as a job server for the SQL Server Agent job. It allows for the configuration of job servers, including the enabling or disabling of job servers, assigning categories to job servers, and assigning proxies to job servers.

This stored SQL server procedure for sp_ad_jobserver has the following parameters:

@job_server_name: The name of the job server to be added.
@target_server_name: The name of the target server to which the SQL Server Agent will connect.
@server_type: The type of server to be added. This can be either a local server or a remote server.
@enabled: A bit value that indicates whether the job server should be enabled or disabled.
@category_name: The name of the category assigned to the job server. This can be used to group similar job servers together.
@proxy_id: The ID of the proxy associated with the job server.
@job_server_id: The ID of the job server that is returned by the stored procedure.

Adding Steps using Sp_add_jobstep Query

The stored procedure sp_add_jobstep adds a new step to an existing SQL Server Agent job. It is part of the SQL Server Agent stored procedures.

The SQL server procedures of the sp_add_jobstep have the following parameters:

@job_name: The name of the job to which the step is added.
@step_name: The name of the new step.
@step_id: The step ID of the new step.
@subsystem: The subsystem to be used for the new step.
@command: The command or Transact-SQL statement to be executed by the new step.
@output_file_name: The name of the output file to be created.
@flags: For the job step, this provides Additional options.
@retry_attempts: The number of times to attempt to execute the step before failing.
@retry_interval: The interval between retry attempts.
@os_run_priority: The priority level for the job step.
@on_success_step_id: The step ID of the step to execute if the current step succeeds.
@on_fail_step_id: The step ID of the step to execute if the current step fails.
@on_success_action: This value shows what should be done following the successful completion of the job phase. The shown values are all possible:

Value	Definition
1	Returns success and then quits from job
2	Returns failure and then quits from job
3	Goes to the next step of the job
4	Goes to step on_success_step_id

@on_success_action

Sp_add_jobschedule Query

SP_ADD_JOBSCHEDULE is a stored procedure in Microsoft SQL Server that is used to create a new job schedule in a specified SQL Server Agent job. This stored procedure is used to define when a job should run and the frequency and duration of the job’s execution.

The stored SQL server procedures take a number of parameters, including:

@job_name: The name of the job to which the schedule is being added.
@name: The name of the schedule.
@enabled: Specifies whether the schedule is enabled or disabled. @freq_interval: The interval of the frequency, such as the number of days or weeks.
@freq_type: Specifies the type of frequency for the schedule:

Value	Definition
1	Job is executed only Once
4	Daily
8	Weekly
16	Monthly
32	Monthly (depends on freq_interval)
64	Runs when the SQL Server Agent service starts
128	Runs when the computer is idle

@freq_type

@freq_subday_type: Specifies the type of sub-day frequency, such as hourly.
@freq_subday_interval: The interval of the sub-day frequency, such as the number of hours.
@freq_relative_interval: The relative interval of the frequency, such as the day of the month.
@freq_recurrence_factor: The recurrence factor of the frequency.
@active_start_date: The date the schedule becomes active.
@active_end_date: The date the schedule ends.
@active_start_time: The time the schedule becomes active.
@active_end_time: The time the schedule ends.

This stored procedure can be used to create a schedule that runs once or a recurring schedule that runs at a specific frequency. It can also be used to create a schedule that is limited by a start and end date and time.

Creating a SQL job with T-SQL code

Creating a SQL job with T-SQL code in the SQL is a simple process that can help automate tasks that need to be performed on a regular basis. We will be using SQL Server 2019 by Microsoft for the process. Let’s get started.

By default, we will observe that the SQL Server Agent is disabled; we will have to enable it first to create a SQL job. Follow these steps to enable the SQL server agent:

Step 1: Open the SQL Server Management Studio (SSMS) on your system.

Step 2: Connect to the server.

Step 3: Now, in the object explorer tab, navigate to the SQL Server Agent. There you will see a cross icon in the logo, and in brackets disabled will be written.

Step 4: To enable it, right-click the SQL Server Agent icon, select Start, and then click Yes.

After the SQL Server Agent is started, a green icon will appear, and now our server agent is active.

Example 1

Now to automate tasks, let’s create a query that will be automatically executed by the job. Following is the query that will be added to Job.

Now the main part starts. Right-click the SQL Server Agent, then select New, and following that, select Job.

After this, a dialogue box will appear. Here you will find a Name option followed by a text box, where we will declare the name of the job. Let’s say Job 1. After entering the name, click OK.

Note: Please ensure that the Enabled Check box is checked; otherwise, our job will not run.

Now, we will add the steps. It is available just below General on the top-left side; now select New in the bottom-center, and a new dialogue box will appear. Now we will add the step name, then the type will be T-SQL by default; let it be the same, and after that, select the database in which the job will be performed. Now, in the command text box, add the query and then click OK.

Now that the automated job is ready, we will write a query that will execute this job step. First, go to the drop-down menu for SQL Server Agent, and there you will see job_1, which we have just made. By performing right-clicking on it and selecting Start Job at Step, our job step will be executed.

If the job is executed successfully, the following dialogue box appears:

We can use the following query to check the success of the job.

Example 2

Now, using another example, we will execute the following query now by scheduling it. We will create a new job named “job_2” and then add the steps; the process is the same, and the query we will add is as follows:

Now we will schedule the job. There are various options for the same, like executing the job once or recurring, weekly, daily, etc. You can also specify the time and date. Here is an example of the same

After scheduling according to your needs, we can click “OK” and wait for it to execute after the inputted time. Here are the before and after results.

And that’s how we create jobs and use procedures to execute jobs using T-SQL.

Conclusion

With this, we conclude today’s article. We discussed creating jobs in the SQL Server Agent using T-SQL, the language used to interact with SQL Server. We also looked at how to create a job in SQL Server Agent using T-SQL, the Sp_add_job query, the Sp_add_jobserver query, the Sp_add_jobstep query, and the Sp_add_jobschedule query. I hope this article was helpful to you. Feel free to comment down below if you have any queries. Until then, keep coding, and have a great day ahead.

Frequently Asked Questions (FAQs)

How do I script a SQL Agent job?

To script a SQL Agent job, you will need to use the SQL Server Management Studio (SSMS). In the Object Explorer, right-click on the SQL Server Agent, select New Job, and then use the Job Properties window to define the job step, the target server, and the schedule. T-SQL script can be used to modify, create, or delete jobs.

How do you create a SQL job to run a stored procedure every day?

To create a SQL job to run a stored procedure every day, open SSMS, then create a new job to define a schedule for the stored procedure to run. Configure the job to run on the schedule you desire, and then add the stored procedure in the steps tab. Finally, save the job and start it to have the stored procedure run on the daily schedule.

How is T-SQL different from SQL?

T-SQL (Transact-SQL) is an extension of the SQL language, developed by Microsoft and Sybase. It allows for the creation and execution of more complex commands and operations than standard SQL, including the ability to create and manage stored procedures, functions, triggers, and more. T-SQL can also be used to query and manipulate data from multiple sources. It also provides additional capabilities such as control of flow, loops, variables, and other features.

Who should own SQL Agent jobs?

SQL Agent jobs should be owned by the user who is responsible for their operation and maintenance. This user should have the necessary permissions to create, modify, and delete jobs, as well as to start, stop, and monitor them.

How do I create a SQL Agent job to execute a stored procedure?

If we want to execute a stored procedure we use SQL Agent Job, for doing so, first of all, open up SQL Server Management Studio and navigate to the SQL Server Agent folder. Right-click on Jobs and select New Job. Give the job a name and description, and then select the Steps tab. Create a new step and select the T-SQL type. In the Command box, type the name of the stored procedure and press OK. Finally, click OK to save the job.