You are the database administrator for your company. You have been assigned the task of managing alerts andissuing notifications for events occurring in the database.
John is a new employee in the organization. You add an operator for John by using the following Transact-SQL batch:
EXECUTE dbo.sp_add_operator
@name = ‘John’,
@enabled = 1,
@email_address = ‘[email protected]’,
@weekday_pager_start_time = 070000,
@weekday_pager_end_time = 200000,
@pager_days = 10,
@pager_address = ‘[email protected]’; GO
You want John to be notified each time the alert named Alert1 is generated. To add the notification, you issue thefollowing Transact-SQL batch:
EXECUTE dbo.sp_add_notification
@alert_name = ‘Alert1’,
@operator_name = ‘John’,
@notification_method = 1; GO
Which statement is true of the notification received by John for the Alert1 alert?
A.
The SQL Server Agent sends an e-mail notification to John on Mondays and Wednesdays each time thealert is generated between 7:00 A.M. and 8:00 P.M.
B.
The SQL Server Agent sends an e-mail notification to John on all weekdays each time the alert is generatedbetween 7:00 A.M. and 8:00 P.M.
C.
The SQL Server Agent sends pager notifications to John on weekdays each time the alert is generatedbetween 7:00 A.M. and 8:00 P.M.
D.
The SQL Server Agent sends pager notifications to John on Mondays, Tuesdays, and Wednesdays eachtime the alert is generated between 7:00 A.M. and 8:00 P.M.
Explanation:
The SQL Server Agent sends e-mail notifications to John on Mondays and Wednesdays each time the alert isgenerated between 7:00 A.M. and 8:00 P.M. In this scenario, the notification method is set to a value of 1 whileadding the notification. A notification method value of 1 specifies that the notifications will be sent through e-mail. The notification will be sent on Mondays and Wednesdays because the @pager_days argument is set to a valueof 10 while executing the sp_add_operator system stored procedure. The @weekday_pager_start_time , @weekday_pager_end_time , @saturday_pager_start_time , @saturday_pager_end_time , @sunday_pager_start_time , and @sunday_pager_end_time argument values determine when notificationsare sent. The @pager_days argument determines when the operator is on duty and available to receivenotifications. The values for the different days of the week are shown in the following graphic:
In this scenario, the @pager_days argument is set to a value of 10. This value indicates that the notifications willbe received on Mondays and Wednesdays because the sum of the corresponding values for Mondays andWednesdays is 10. The option stating that the SQL Server Agent sends e-mail notifications to John on all weekdays between 7:00A.M. and 8:00 P.M. is incorrect. The e-mail notifications will be sent to John only on Mondays and Wednesdays because the @pager_days argument is set to a value of 10. To receive the notification on all weekdays, the @pager_days argument should be set to a value of 62. This value is the sum of all the values corresponding toweekdays. The option stating that the SQL Server Agent sends pager notifications to John on weekdays between 7:00 A.M.and 8:00 P.M. is incorrect because the SQL Server Agent will not send pager notifications. E-mail notifications willbe sent because the @notification_method argument is set to a value of 1 while executing the sp_add_notification system stored procedure. To send pager notifications, the @notification_method argument should be set to a value of 2. Notifications will be sent to John only on Mondays and Wednesdaysbecause the
@pager_days argument is set to a value of 10. The option stating that the SQL Server Agent sends pager notifications to John on Mondays, Tuesdays, andWednesdays between 7:00 A.M. and 8:00 P.M. is incorrect because the SQL Server Agent will not send pagernotifications. E-mail notifications will be sent because the @notification_method argument is set to a value or 1while calling the sp_add_notification system stored procedure. To send pager notifications, the @notification_method argument should be set to a value of 2. Notifications will be sent to John only onMondays and Wednesdays because the @pager_days argument is set to a value of 10. To send notifications onMondays, Tuesdays, and Wednesdays, the @pager_days argument should be set to a value or 14. This value isthe sum of the corresponding values for Mondays, Tuesdays, and Wednesdays.Objective:
Maintaining SQL Server InstancesSub-Objective:
Manage SQL Server Agent alerts.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)> sp_add_operator (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)> sp_add_notification (Transact-SQL)
