Sunday 11 October 2015

Operator in sql server agent


Operators is nothing but it is a groups or teams or people that can receive the message or alert (electronic notification) when sql jobs has failed or completed or any alerts have been raised.


 With the help of Operator sql server agent notify to the group or teams.

Learn how to create Operator in sql server agent step by step

Open sql server management studio (SSMS).
Start the sql server agent.
In sql server agent you will get the Operator.

Now click on New Operator.
You will get the new window New Operator.
Select General tab.
Write the name of operator and check the Enable check box.
Select the Notification options. Below are the notification options are available
Ø  E-Mail
Ø  Net send
Ø  Pager
Now we can schedule our Operator according to our business hours.
                


  
Now go to Notification tab. You will set on which action you want to notify the operator either Alert or Jobs. Select the option you will get the list of jobs or Alerts.
You can select the notification options (Email, Pager or net send).

Click ok. Now operator is created.
  
You can also create operator using sql script. You can generate the sql script.

Click on the script and select Script Action to new Query Window. You will get below sql query.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'My First Operator',
              @enabled=1,
              @weekday_pager_start_time=80000,
              @weekday_pager_end_time=180000,
              @saturday_pager_start_time=80000,
              @saturday_pager_end_time=180000,
              @sunday_pager_start_time=80000,
              @sunday_pager_end_time=180000,
              @pager_days=127,
              @email_address=N'bageshkumarsinghmca@gmail.com'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'My Database Alert',
 @operator_name=N'My First Operator', @notification_method = 1
GO



Now operator is ready to use.



 Now Enjoy. 

Popular Posts