Create a JOB in SQL Server Management Studio

Introduction

This article describes how to create a SQL Server Agent job in SQL Server by using SQL server management (SSMS) studio.

Getting Started

The following steps to create a job in SQL Server using SQL server management studio. This demonstration is conducted in Visual studio 2017 and SSMS 2019. But these steps are working for any version of SQL and SSMS

1. Open SQL Server Management Studio and log in with your SQL instance.

2. In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.

3. Click the plus sign to expand SQL Server Agent.

4. Right-click the Jobs folder and select New Job...

5. A new Job window will appear, fill the job name and description field and click on schedule.

   

   New SQL Agent JOB Window

6. The New Job Schedule window will appear, fill the required details and click on the OK button

   

   SQL Server Task Scheduler Window

7. Again click on the OK button in New Job Window.

8. Right-click an existing job, and then click Properties, in the Job Properties dialog, click the Steps page, and then click New.

9. Type a job Step name, in the New Job Step dialog

   

   SQL job Steps Window

10. Click Transact-SQL Script (TSQL), in the Type list.

11. Type the Transact-SQL command batches, in the Command box or click Open to select a Transact-SQL file to use as the command.

12. Click Parse to check your syntax. The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.

13. Click the Advanced page to set job step options, such as:

    1. What action to take if the job step succeeds or fails

    2. How many times SQL Server Agent should try to execute the job step and the file or table where SQL Server Agent can write the job step output

    

    SQL job Steps Window

14. Click on the OK button to complete the process and you are done with the creation of the job.

Note:- As i told above of this blog, only members of the sysadmin fixed server role can write job step output to an operating system file and If you are a member of the sysadmin fixed server role and you want to run this job step as a different SQL login, select the SQL login from the Run as user list.

What is SQL JOB

SQL Server jobs are a specified series of operations performed sequentially like Windows service in .NET; it is controlled by SQL Server Agent.

A job can perform a wide range of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands, and queries, or Replication tasks.

Like Windows Services, it also can run repetitive or scheduled tasks, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration.

SQL Job can be written to run on the local instance of SQL Server or on multiple instances across an enterprise. It can be created or edited only by its owner or members only of the sysadmin role, hence a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role.

Summary

From the above demonstration, I hope you have learned how to create a SQL JOB in SQL Server and enjoyed it a lot

Thanks