A MAPI free world!

Home About me and Nucleus Partners Articles, tips etc. My blog Links Microsoft articles Forums Course demo files

Overview The mail support that ships with SQL Server up to SQL Server 2000 uses MAPI. Now, MAPI can be fine for an interactive mail client, but it is sometimes a mess to setup and get working with SQL Server. Important notice As of SQL Server 2005, we have Database Mail. This is an email implementation that doesn't use MAPI, it uses SMTP directly. Read about "Database Mail" and sp_send_dbmail in Books Online. More information You send email from T-SQL in SQL Server using xp_sendmail. SQL Server Agent can also send email: notifications after job executions and also for event and performance condition alerts. However, above uses MAPI. In order to get that working, you need to install Outlook on the SQL Server machine (I recommend Outlook 2000, I've had problems with 2002 and 2003). You then need to login on the SQL Server machine using the SQL Server and Agent service account and create the mail profile. And finally specify this mail profile in Enterprise Manager for both SQL Server and SQL Server Agent. Below you find recommendations and tips on how to use email from SQL Server and SQL Server Agent without using MAPI or Outlook. Sending email from SQL Server This is the easy one. Just download and install xp_smtp_sendmail from SQLDev.Net. This uses direct SMTP communication and doesn't require any MAPI or Outlook installation at all. Information about how to install and use xp_smtp_sendmail can be found at the SQLDev.NET web site. Job notifications from SQL Server Agent This is also quite straight-forward. Download and install xp_smtp_sendmail from SQLDev.Net. You don't use the operators or mail support in SQL Server Agent. Instead, you create a couple of extra job steps in your job, used to finish off the job processing. Say you have a job with three job steps, and you have default flow control for each step: As long as it is success, you continue to next step. If you have a failure, the job ends and reports failure. Create two jobsteps at the end of the job, one uses xp_smtp_sendmail to send email on failure, the other on success. Say we name these "MailOk" and "MailFail". For each regular job step, set On Failure to go to MailFail. For each regular job step, except the last one, set On Success to go to next step. For the last regular job step, set On Success to go to the "MailOk" step. For the MailOK step: Set On Success to "End job Reporting Success. Set On Failure to "Quit the Job Reporting Failure". For the MailFail step: Set both On Success and On Failure to "Quit the Job Reporting Failure". I also recommend that you define an output file for each jobstep (except for the email jobsteps) and include that output file in the email using the @attachments parameter of xp_smtp_sendmail. This way you will receive an email each time the job was executed, the subject states whether it was success of failure and attached to the email you have a file with all output from the commands you executed - including error messages! Event alert notifications from SQL Server Agent Use the "Db Maint SMTP Alerter" from Db Maint.