設定「警示(Alerts)」來回應所發生的錯誤，以「錯誤嚴重性(Error Severities)」為例

當 SQL Server Database Engine 產生錯誤時，錯誤的嚴重性會指出 SQL Server 所發生的問題類型。

下表列出和描述 SQL Server Database Engine 所產生之錯誤的嚴重性層級。

---

以下的實作練習，將建立「警示」，來回應Database Engine 錯誤嚴重性(Error Severities)
回應的嚴重性層級，是在層級 17 以上，而且是已經寫入到 Windows 應用程式記錄中。

任務1：建立「警示」來回應嚴重性層級在17以上的錯誤事件
請執行以下的範例程式碼：

PRINT N'準備安裝「警示」...'
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 17 錯誤')
OR ((severity = 17) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 17 錯誤',
@message_id = 0,
@severity = 17,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 18 錯誤')
OR ((severity = 18) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 18 錯誤',
@message_id = 0,
@severity = 18,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 19 錯誤')
OR ((severity = 19) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 19 錯誤',
@message_id = 0,
@severity = 19,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 20 錯誤')
OR ((severity = 20) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 20 錯誤',
@message_id = 0,
@severity = 20,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 21 錯誤')
OR ((severity = 21) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 21 錯誤',
@message_id = 0,
@severity = 21,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 22 錯誤')
OR ((severity = 22) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 22 錯誤',
@message_id = 0,
@severity = 22,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE name = N'發生：嚴重層級. 23 錯誤'
OR ((severity = 23) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 23 錯誤',
@message_id = 0,
@severity = 23,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 24 錯誤')
OR ((severity = 24) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 24 錯誤',
@message_id = 0,
@severity = 24,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
FROM msdb.dbo.sysalerts
WHERE (name = N'發生：嚴重層級. 25 錯誤')
OR ((severity = 25) AND
(message_id = 0) AND
(database_name IS NULL) AND
(event_description_keyword IS NULL) AND
(performance_condition IS NULL))))
EXECUTE msdb.dbo.sp_add_alert
@name = N'發生：嚴重層級. 25 錯誤',
@message_id = 0,
@severity = 25,
@enabled = 1,
@delay_between_responses = 10,
@database_name = NULL,
@notification_message = NULL,
@job_name = NULL,
@event_description_keyword = NULL,
@include_event_description_in = 5; -- Email and NetSend
GO

PRINT ''
PRINT N'已經完成安裝.'
GO

-- 查詢已經安裝的「警示」
EXECUTE msdb.dbo.sp_help_alert;
GO

請參考下圖所示：

---

由上，已經完成建立「警示」的作業。

請再整合到「操作員(Operator)」上，設定發生這些「警示」時，要使用「操作員」進行通知作業。

---

若是要刪除先前所建立的「警示」，可以執行以下的範例程式碼：

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 17 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 17 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 18 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 18 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 19 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 19 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 20 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 20 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 21 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 21 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 22 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 22 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 23 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 23 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 24 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 24 錯誤'
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生：嚴重層級. 25 錯誤')
EXEC msdb.dbo.sp_delete_alert @name=N'發生：嚴重層級. 25 錯誤'
GO

---

參考資料：
監視和回應事件
http://msdn.microsoft.com/zh-tw/library/ms191508.aspx

Database Engine 錯誤嚴重性
http://msdn.microsoft.com/zh-tw/library/ms164086%28SQL.90%29.aspx

讀我檔案_警示範例
http://technet.microsoft.com/zh-tw/library/ms161554.aspx

Microsoft SQL Server Community Projects & Samples
http://sqlserversamples.codeplex.com/Wikipage

Microsoft SQL Server Product Samples: Scripts
http://msftscrptprodsamples.codeplex.com/Wikipage