The SQL Server Agent job is the specified action or series of actions that the SQL Server Agent will execute at any specific date time.
Sometimes you may get a requirement to execute certain SQL scripts, Stored procedures, or backups scripts at any specific time or any time interval. It becomes very difficult to execute each scripts manually at a specified time. In such case, you can schedule a jobs for executing scripts automatically at specified time using SQL Server Agent .
Suppose you have a stored procedure which fetch the data from employee table and copy into a table named employee_log, and you have to execute this stored procedure at specified time using SQL Jobs.
Lets go through the following steps to creating a SQL Jobs for this task.
Step 1> Lets first create a employee_log table.
Create table employees_log (EmployeeId int ,lastName varchar(50) ,FirstName varchar(50) ,Birthdate datetime ,HireDate datetime ,City varchar(50) ,Address varchar(100))
Step 2> Create a stored procedure that gets the data from employee table and insert it into employee_log table.
Create Proc fetch_employeeData as begin Insert into employees_log select EmployeeId, LastName, FirstName, Birthdate, HireDate, City, Address From Employees end
Step 3> Create a SQL job scheduling using following steps.
>Click on SQL server Agent > next, Click on Jobs
Step 4> Click on new job..
Step 5> A New job window appears,
>General > provide job name > then , Description > Enabled check box > click on OK button.
Step 6> Next, click on Steps Page > A new job step window Appears.
> Click on General > Provide Step Name > Select data base name to execute jobs > Provide your scripts/ stored procedure name
> Next ,click on OK button
Step 7> Click on Schedule page > A new job Schedule window appears
> Given any Schedule name and tick on Enabled checkbox
> Set Scheduler frequency (Run job weekly ,daily … etc
> Daily frequency ( set any specific time you want to run job , you can also select starting and ending date)
>Scheduler duration , you can also select starting and ending date
> click on OK button
Step 8> YOu can see in below screenshot, a Job is created then click on OK button.
Step 9> You can also check whether a job is created or not, Go to SQL Server Agent folder and expand Jobs folder there you can see a newly created job.
Step 10>Before executing the job, lets verify the records in employee and employee_log table.
As you can see, employee_log table is empty while employee table having 7 records.
Select * from employee_log
select EmployeeId, LastName, FirstName, Birthdate, HireDate, City , Address From Employees
Step 11 > As Job will be executed automatically at specified time that you have provided during the job creation. But you can also test job manually executing the Job.
> right click on Job name > Click on Start Job at step..
Step 12> Here you can see, Job is executed and returns success message > click on close button.
Step 13>Lets check whether the records are inserted into Employee_log table or not.
As you can see , now employee_log table contains 7 records which are inserted through SQL Jobs.
Youe can also track the job execution time with failure and success status. Go to Jobs folder then right click on job and click on View History in context menu.
SQL Server Database Mail Configuration
2,309 total views, 1 views today