You are the SQL administrator for your company. A SQL Server 2008 computer named Sql3 has several alertsconfigured.
One of the alerts was created using the following Transact-SQL statement:
EXEC dbo.sp_add_alert
@name = ‘Backup Alert’,
@enabled = 1,
@message_id = 0,
@severity = 20,
@notification_message = ‘Error. The database will be backed up.’,
@job_name = ‘Back up the Production Database’;
You need to edit the alert so that the alert has a message ID of 56100. Which Transact-SQL statement should you use?

A.
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100;

B.
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 1;

C.
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 0;

D.
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 56100;

Explanation:

You should use the following Transact-SQL statement:
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 0;
To configure a message ID value, the severity level must be set to 0. If the alert has a severity level configured,the message ID value must be set to 0. You cannot have a value for both the message ID and severity levelconfigured on an alert. You should not use the following Transact-SQL statement:
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100;
This statement does not set the severity level to 0. A message ID cannot be assigned if the severity level is set to20, which is what the original alert creation in this scenario specified. You should not use the following Transact-SQL statement:
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 1;
This statement does not set the severity level to 0. A message ID cannot be assigned if the severity level is set to1. You should not use the following Transact-SQL statement:
EXEC dbo.sp_update_alert
@name = ‘Backup Alert’,
@message_id = 56100,
@severity = 56100;
This statement does not set the severity level to 0. A message ID cannot be assigned if a severity level isconfigured.

Objective:
Maintaining SQL Server Instances

Sub-Objective:
Manage SQL Server Agent alerts.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server