Integrate Database Mail
Database Mail is a shiny new feature of SQL Server, it is the successor of SQLMail, so use this one instead. It doesn’t rely on any external programs (e.g. Outlook) in contrast of its predecessor. It uses a simple SMTP server.
You should be familiar with one stored procedure: msdb.dbo.sp_send_dbmail, which accepts a whole lot of parameters. The most important ones are:
- @profile_name: specifies the Database Mail profile to use when sending the mail.
- @recipients: the recipients of the message, in a comma delimited list.
- @(blind_)copy_recipients: CC and BCC.
- @subject: the subject of the message.
- @body: the content of the message.
- @query: the query to attach to (or place in the body of) the message.
- @execute_query_database: the name of the database the query should be executed in.
- @append_query_error: a bit value. If you set it to one, it sends the mail if the specified query fails, along with the error message.
Now let’s see a valid example:
msdb.dbo.sp_send_dbmail @profile_name=’myMail’, @recipients=’firstname.lastname@example.org’, @query=’SELECT * FROM dbo.MyTable’ @execute_query_database=’ImportantDatabase’, @body=’Here is the daily report:’, @subject=’Daily report NO 114343’
However, before you can send a mail, you need to set up Database Mail. This can be done easily in SMSS, hitting on the Management section of the server in Object Explorer, then double clicking Database Mail. And you need to have the Enterprise or Evaluation version. Experiment and have fun!