SQL Server Agent can be configured to notify an operator when certain error messages has been written to the eventlog by SQL Server. Unfortunately, this is based on MAPI, and installing and getting MAPI to functioning properly for SQL Server and SQL Server Agent can be a mess. We already have a substitute for the MAPI based xp_sendmail: xp_smtp_sendmail from SQLDev.Net (home page).

The Db Maint SMTP alerter allow you to be notified using SMTP (using xp_smtp_sendmail) instead of MAPI. This mean that you do not have to install Outlook and configure MAPI on your SQL Server.

1. Download and install xp_smtp_sendmail from SQLDev.Net.
2. Download the files for Db Maint SMTP Alerter. There is no installation program, just download the zip file, create a directory and extract the files to this directory.
3. Use Enterprise Manager to create the operators. Specify an email address for each operator. Important: Disable each operator. If you don't disable the operators, then SQL Server Agent will also try to send mail to them and write failure messages tn the eventlog.
4. Create the desired alerts in Enterprise Manager. For each alert, specify the operator(s) you want to be notified.
5. Run the client (DbMAlerterClient.exe) to create the needed tables in msdb, and also configure the SMTP mail settings.
6. Schedule the server (DbMAlerterServer.exe), which will read the eventlog and submit email to the operators as configured. Recommended scheduling interval is between once per minute to once per 10 minutes. DbMAlerterServer.exe will attempt to login to the SQL Server using Windows logins and it takes one command-line parameter: the name of the SQL Server to connect to, as in
   
   C:\Program Files\DbMaintSmtpAlerter\DbMAlerterServer MachineName[\InstanceName]

• .NET framework 1.1 or later. It is likely that this already is installed on your machine, if not you will get an error message that the application failed to initialize. You can install the framework using Windows Update, or download it from microsoft (search for "framework redistributable" at http://www.microsoft.com/downloads).
• Xp_smtp_sendmail which you can find at SQLDev.Net., where you also find download and installation instructions.

• Currently only support one operator. Only the first operator for the alert will receive email. This will be changed in an upcoming release.
• Only support installations where SQL Server writes the error messages in the default English language.
• Will not perform NET SEND. If you want net send alerting, create an operator for the net send, do not disable this operator and specify this operator as well for the alert; with net send notification (just as a regular Agent notification).
• If NonAlertableErrors has been configured in the registry and such are encountered, the email will not be submitted (just as Agent does it). However, a warning will not be written to the event log when this happens.