RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Sending E-mail from SQL Server 200X

From monitoring e-mail messages to distributing reports for users, SQL Server's database mail proves quite useful. Learn how to use it in SQL Server 2000 and SQL Server 2005/2008.

ending email from SQL Server can be a useful way to keep yourself or the system's administrator informed of any errors and alerts, but depending on your version of SQL Server, the setup can be quite involved or relatively simple. With SQL Server 2000, the setup requires a heavy email client, such as Microsoft Outlook, or a rather convoluted stored procedure. SQL Server 2005 vastly improved the process by adding email functionality to Management Studio. (The feature remains in SQL Server 2008.)

This article explains the processes for sending email from SQL Server 2000 and from SQL Server 2005/2008.

Send Email from SQL Server 2000

To send email from SQL Server 2000, you can use Outlook (or another email client) or Blat, a command-line executable SMTP email client that allows you to forego Outlook and a POP3 account (if you're not using Exchange). Blat is flexible enough to handle attached reports and formatting, and it's free.

To use the Blat option, you would wrap the call to the command line in a function or stored procedure. For instance, you could use the following statement to call the stored procedure in Listing 1:

Exec stp_BlatMail 'ServerName', 'fhtapia@gmail.com', 
 'System Maintenance: Low Memory', 'D:\Data\TempFiles\MemoryRpt.txt'
The stored procedure in Listing 1 contains simple error handling to prevent Blat from wasting CPU cycles. For example, if an @To variable is not populated, the process aborts. Although this particular code doesn't validate the email address, you could easily add code to do so. The @cmd variable contains the constructs of the command-line utility, as shown in Table 1.

Table 1. The Constructs of the Command-Line Utility
Construct Description
- The body of the message is at the end of the parameter.
-t To email address
-sender A newer syntax that works well for authenticated connections
-from From email address
-f Display name for your email address
-org Organization name; a great place to identify the name of your company
-x Email header information
-noh Prevents your header information from displaying Blat's homepage
-noh2 Prevents a header entirely
-server Email server IP or name
-port Typical SMTP port is 25.
-u User ID
-pw Password
-body Your email message; for small reports, you could use this part instead of an attachment.

If you have no attachment, just leave that field empty and the procedure will ignore the construct in the command line.

Author's Note: Running this process involves using xp_cmdshell. That means your average users can't execute it. Consider queuing messages in a table and then having an alert job run against the table. Another method is to set up a SQL Server proxy agent user account that allows users to execute xp_cmdshell. Be careful with this method because it could compromise your server's security.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date