You are the database administrator for your company. The company’s network consists of a single ActiveDirectory domain named nutex.com.
You are configuring the SQL Server Agent operator information for a user named Paul.
You want to ensure thatPaul receives notifications by pager Monday through Friday from 9:00 A.M. to 6:00 P.M.
Which Transact-SQL should you run?
A.
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 62; GO
B.
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 32; GO
C.
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
D.
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 127; GO
Explanation:
You should run the following Transact-SQL:
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 62; GO
The SQL Server Agent service allows notifications to be sent through operators when a job has completed or analert is triggered. Operators are aliases for users or groups that can receive notifications. You can createoperators by using SQL Server Management Studio or using the sp_add_operator system stored procedure.While configuring an operator, you must ensure that you specify a unique name for the operator and provide theoperator’s contact information. To specify the start and end time for all weekdays when the operator can receivenotifications by pager, you should use the @weekday_pager_start_time and @weekday_pager_end_time parameters. The time for these parameters must be entered by using the HHMMSS format. To specify the daysthe operator will receive notifications, you should use the @pager_days parameter. The valid values for thisparameter are from 0 to 127, where 0 indicates that the operator is never available for receiving pager messages.The value for each day of the week is assigned a value as follows: Sunday = 1, Monday = 2, Tuesday = 4,Wednesday = 8, Thursday = 16, Friday = 32, and Saturday = 64. The value for the @pager_days parameter is @weekday_pager_start_time = 090000, @weekday_pager_end_time = 180000, @pager_days = 64; GO calculated by adding the values of the required days for which notifications should be sent. For example, thevalue for weekdays from Monday through Friday would be 2+4+8+16+32 = 62. You should not run the following Transact-SQL:
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 32; GO
The value for the @pager_days parameter is calculated by adding the values of the required days for whichnotifications should be sent. A value of 32 is for Friday. Specifying 32 for the @pager_days parameter will allowPaul to receive notifications by pager only on Fridays. You should not run the following Transact-SQL:
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 64; GO
The value for the @pager_days parameter is calculated by adding the values of the required days for whichnotifications should be sent. A value of 64 is for Saturday. Specifying 64 for the @pager_days parameter willallow Paul to receive notifications by pager only on Saturdays. You should not run the following Transact-SQL:
USE msdb; GO
EXEC dbo.sp_add_operator
@name = N’Paul’,
@enabled = 1,
@email_address = N’paul’,
@pager_address = N’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@pager_days = 127; GO
The value for the @pager_days parameter is calculated by adding the values of the required days for whichnotifications should be sent. Specifying 127 for the @pager_days
parameter will allow Paul to receivenotifications by pager from Sunday through Saturday. In this scenario, you want to ensure that Paul receivesnotifications by pager from Monday through Friday. Therefore, you should specify a value of 62 for the @pager_days parameter.Objective:
Maintaining SQL Server InstancesSub-Objective:
Manage SQL Server Agent operators.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 > Operations > Administration > AutomatingAdministrative Tasks > (SQL Server Agent) > Monitoring and Responding to Events > Defining Operators MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics > Automated Administration How-to Topics (SQL Server Management Studio) >How to: Create an Operator (SQL Server Management Studio)