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


Sending E-mail from SQL Server 200X : Page 2

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.


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 = 'UserAddress@company.com',
    @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.

Francisco Tapia is a database administrator working for a large manufacturing company. He has been working with SQL Server since version 6.5. You can read more from Tapia at sqlthis.blogspot.com.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date