Data Management

Part IV: Configuring SQL Mail

We will finish off  this series with showing you how to take advantage of the stored procedure xp_sendmail through T-SQL.

Note: Select the link to read the entire blog series on SQL Mail.

Configure xp_sendmail

Now that you have configured SQL Mail, you can take advantage of the xp_sendmail stored procedure that allows you to send messages through T-SQL. In my example, I am going to send an e-mail that will tell me which version of SQL Server I currently have running. Figure A shows how this would break out.

The parameters for using xp_sendmail are as follows:

xp_sendmail {[@recipients=] 'recipients [;...n]'}

 [,[@message=] 'message']

 [,[@query=] 'query']

 [,[@attachments=] 'attachments [;...n]']

 [,[@copy_recipients=] 'copy_recipients [;...n]'

 [,[@blind_copy_recipients=] 'blind_copy_recipients [;...n]'

 [,[@subject=] 'subject']

 [,[@type=] 'type']

 [,[@attach_results=] 'attach_value']

 [,[@no_output=] 'output_value']

 [,[@no_header=] 'header_value']

 [,[@width=] width]

 [,[@separator=] 'separator']

 [,[@echo_error=] 'echo_value']

 [,[@set_user=] 'user']

 [,[@dbuse=] 'database']

With the above method, you can create triggers in your SQL code to notify administrators, including yourself, via e-mail if certain conditions occur. For example, you might set up notifications for long running queries, the deletion of certain tables, the rebuilding of indexes, backups failing, and a host of other database-related inquiries.

As you can see, SQL Mail can be very powerful. I have introduced you to the possibilities of SQL Mail and how to incorporate it into your infrastructure. In addition, I walked you through the process of configuring SQL Mail and testing it to make sure the necessary pieces work.

Your next step will be to continue testing with various options, and then begin using this solution in a production environment to automatically notify you of any SQL Server problems, issues, or disasters.

Editor's Picks

Free Newsletters, In your Inbox