Microsoft SQL Server 2008 R2 Unleashed- P51:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 444 CHAPTER 15 Database Mail FIGURE Creating a SQL Server event alert with a Database Mail notification. On the left side of the alert properties dialog you click the Response link. Then you check the Notify Operators check box and in the Operator list check the Email check box to the right of the Test Database Mail Operator grid row. Finally you click OK to close and save the new custom alert. Testing the Alert Notification To test your new alert notification you open a new query window in SMSS and enter the following code USE AdventureWorks2008 go RAISERROR This is an alert mail test 10 1 WITH LOG go This is an alert mail test Because you specified WITH LOG this simple statement writes an event to the Windows Event log which in turn triggers the alert because the database context message text and severity all match the conditions of the alert. An email message should have appeared in your inbox indicating the alert s successful triggering. This message should contain body text such as this DATE TIME 5 7 2009 9 00 45 PM DESCRIPTION Error 50000 Severity 10 State 1 This is an alert Download from Related Views and Procedures 445 mail test COMMENT None JOB RUN None Related Views and Procedures To report on the status of all your Database Mail objects without relying on wizards and properties pages you need some tabular views and stored procedures. msdb contains many system tables views and corresponding stored procedures that make this task easy. The following section lists the tables or views and their columns noting the stored procedure if any that you can use to read from them. Viewing the Mail Configuration Objects The first set of msdb objects we ll review are those related to system objects such as profiles profile security and accounts sysmail_profile Contains basic profile data including the unique profile_id name description last_mod_datetime and last_mod_user name. You execute sysmail_help_profile_sp to retrieve this data by .
