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=’[email protected]’, @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!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s