By: Tibor Nagy
Overview
Scheduling is a very useful feature of every database management software. In the MS SQL Server the SQL Server Agent acts as the scheduler and it executes the tasks.
Explanation
SQL Server Agent is the equivalent of the MySQL Event Scheduler. It is part of every edition except MS SQL Express. It can be accessed from SQL Server Management Studio.
However, the MS SQL implementation is very different from the CREATE EVENT statement syntax in MySQL. You have to create jobs, job steps and schedules separately. There are two ways to perform this task, you can either use the GUI or create Transact-SQL statements.
The general steps to create a scheduled job using T-SQL are the following:
- Execute sp_add_job to create a job.
- Execute sp_add_jobstep to create each job step.
- Execute sp_add_schedule to create a schedule. You can define more schedules, e.g. run at 3am and 11pm.
- Execute sp_attach_schedule to link the schedule to the job.
- Execute sp_add_jobserver to set the server for the job.
The use of the GUI is very self-explanatory. Right click on Jobs and select New job from the menu. The following screen appears:
After you entered the job properties, click the Steps page in left side pane, then click New. You will get this screen:
After you entered all the job steps, click the Schedules page in left side pane, then click New. You will get this screen:
Now you have successfully scheduled a job using MS SQL Server Agent.
Additional Information
- SQL Server Agent Job Management article on MSSQLTips
- Implementing Jobs reference article on MSDN
- SQL Server Agent category on MSSQLTips
