3 Slick Scripts for Database Mail Alerts and Reports

Database Mail enables you to send e-mail messages from the SQL Server Database Engine. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. After you had completed the simple steps of enabling Database Mail, configuring your SMTP settings and adding the Database Mail account to a Database Mail profile, you will be ready to start using Database Mail to automate notifications and reports that keep you up to the minute on the status of key performance indicators.

In my latest white paper, Hands-On Guide to Automatic Notifications and Scheduled Reports with Database Mail, we go step-by-step through the creation of a Database Mail account, then I show you how to use that account to receive automated reports and alerts.

1. Database Mail Script for New Database Alerts

After taking you step by step through the process of establishing and testing Database Mail accounts on your internal server, on Gmail, or on other public servers, the white paper provides you scripts for automating important business alerts and reports.

The first of these three helpful scripts is for generating an alert upon the creation of one or more new databases on the server. The script first queries the SQL server to see if any new databases have been created in, say, the previous hour.

If new databases do appear on the server, the script then sends an email to the recipients listed in the script, informing them of the filenames of any new databases and who their owners are.

2. Database Mail Script for Key Performance Indicator Alerts (KPI Alerts)

With Database Mail, you can generate a remarkable number of useful alerts and reports without expensive third-party software or support. For example, you could set a list of sales-related goals that, when met, would trigger alerts.

Database Mail allows you to specify Key Performance Indicators (KPIs), and then be notified when they fall above or below threshold levels. The white paper also provides a script for querying an SQL database, extracting sales information, comparing it to pre-determined threshold levels, and then generating an email alert depending on the results.

The script used as an example in the whitepaper shows how to establish a list of who gets which alerts. It also shows how to change the threshold settings so that alerts happen more or less frequently.

3. Database Mail Script for Sending Scheduled Reports

Sometimes a report is better than an alert. A daily sales report might be more useful to managers than case-by-case alerts. The white paper shows how to create a system-stored procedure for generating CSV reports from SQL databases using Database Mail.

The script begins with enabling xp_cmdshell. Make sure xp_cmdshell is permitted in your environment. Then create your stored procedure: a list of inventory with the quantity sold in descending order, for example.

Next, create a folder to hold the reports generated by the script. The folder will be used to hold reports that will be distributed according to the rules in the script.

The script included in the white paper shows you how to automate the login of a user with privileges that allow compiling the specified report. Once an authorized user has signed in, the scheduled report can be pulled.

The script further instructs the server to generate a CSV file from the data and mail it as an attachment to the recipients specified in the script. The whitepaper has all the details on how to test your script and verify the right person is getting the right report at the right intervals.

You can download the full white paper, Hands-On Guide to Automatic Notifications and Scheduled Reports with Database Mail[SOK4] , and get these three slick scripts for using Database Mail alerts and reports.

If you would like assistance setting up Database Mail or responding to the alerts and reports that it generates, please contact us. Datavail is the leader in remote database administration. We can help you use these tools to cut costs while improving the performance of your databases and your team. Contact us today for a custom solution to your database problems.