Sending E-mail from SQL Server 200X

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', '[email protected]',  'System Maintenance: Low Memory', 'D:DataTempFilesMemoryRpt.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.

Send Email from SQL Server 2005/2008

You don’t need a complex stored procedure in SQL Server 2005 or SQL Server 2008. You can set up most of the process in Management Studio as follows:

  1. In Management Studio, expand the Management folder and right-click Database Mail.
  2. Choose Configure Database Mail to launch the Database Mail Configuration Wizard and click Next.
  3. If the Database Mail service isn’t running, the wizard will display the message in Figure 1. Click Yes. If the service is running, continue to Step 4.
  4. At this point, the wizard helps you create a new profile, as shown in Figure 2. You can add as many SMTP accounts as you need. Try to give each account a meaningful name and a comprehensive description.

    Figure 1. Enable Database Mail Service: The Database Mail service isn’t running.
    Figure 2. Specify a New Email Profile: The wizard helps you create a new profile.

  5. Click Add to display the New Database Mail Account dialog box shown in Figure 3. Enter the account’s email address, display name, and a ‘reply to’ email address. Usually, the two email accounts will be the same, but it isn’t necessary to use the same account.
    Figure 3. Specify Your Connection Details: Here is the New Database Mail Account dialog box.
  6. Check the This Server Requires a Secure Connection (SSL) option if your email server uses SSL security. Most often, Windows Authentication is the way to go when choosing security. It’s the least complicated route. If you use the local system account, you must know the user name and password. Avoid Anonymous unless you know what you’re doing.
  7. After setting all the options, click OK to return to the New Profile dialog box. It should display your new account, as shown in Figure 4. Continue adding accounts as necessary.
  8. When you’re ready to continue, click Next. The default settings are adequate (see Figure 5), but you may consult with your email administrator to get the right settings. Click Next and then Finish.

    Figure 4. Add Accounts as Needed: The New Profile dialog box displays your new account.
    Figure 5. Changing the Default Settings: Change the necessary settings as determined by your environment.

Now that you have a profile up and running, you can easily access the built-in email functionality using the following code:

@squery = 'SELECT * from sysfiles'EXEC msdb.dbo.sp_send_dbmail    @profile_name = 'DBMail',    @recipients = '[email protected]',    @query = @squery,    @subject = @message,    @attach_query_result_as_file = 1 ;

When you call the stored procedure, pass the appropriate Transact-SQL statement or use a default, as shown. Be sure to update the parameter text to suit your needs. Notice that @recipients appears to reference a list. That’s an easy way to send the same information to a group. Set @attach_query_result_as_file = 1 to have your results sent as an attachment, and set @attach_query_result_as_file = 0 if you are sure that the results are not too long.

Mail’s Up!

You now have a valid method for sending email in SQL Server 2000?for those legacy systems?and a clear method for configuring those SQL Server 2005 and SQL Server 2008 machines to send out mail by the dozens.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

6 Tips for Setting Up a Decentralized Exchange

6 Tips for Setting Up a Decentralized Exchange

There’s no doubt that cryptocurrency is a complex and divisive topic in the modern financial landscape. There are those who are convinced that it’s nothing more than a bubble, but both who are well-informed are able to see the ways in which cryptocurrency can help them both build their fortune

user experience with someone on their phone

5 Ways to Improve Your Customers’ User Experience

They say you can’t judge a book by its cover, but just because they say that doesn’t mean it’s true. Consider how often you choose one sort of product over another just because it appeals to you. Then think about how often you’ve stopped using something because, simply put, it

How to Manage Your Finances after Buying a Home

How to Manage Your Finances after Buying a Home

Buying a home is a milestone in the journey of life – it’s one way to invest your money and create lasting memories. Now you know everything about a home purchase, home mortgage, and what is a conventional 97 loan but do you know what’s next after you sign the

Windows Logging is one of the vital aspects of any Windows system administration. However, it is mostly overlooked until the system develops a problem. This is because logs contain important information needed to troubleshoot and resolve system issues.

The Fundamentals of Windows Logging

Windows Logging is one of the vital aspects of any Windows system administration. However, it is mostly overlooked until the system develops a problem. This is because logs contain important information needed to troubleshoot and resolve system issues. Without it, tech experts might find it difficult to track a computer’s

Interstitial Ads: Best Practices for Successful Campaigns

Interstitial Ads: Best Practices for Successful Campaigns

Interstitial Ads: Best Practices for Successful Campaigns Interstitial ads are full-screen advertisements that appear to grasp the attention of on-site prospects, creating opportunities for brands seeking effective ways to communicate their proposition of value. With such an attention-grabbing format and high-impact visuals, it’s no wonder why interstitial advertising is proving