Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 22, 2004

Automate with SQL Mail - Page 2

By Steven Warren

Configure SQL Mail

In order to configure SQL Mail, you must open the SQL Server Enterprise Manager from the Start menu, then explore your SQL Server and expand your Support Services folder, as shown in Figure G.

Figure G
Click for larger image

Open the SQL Server Enterprise Manager.

Now right-click on Support Services and choose Properties. From the dropdown menu, choose the profile you just created (Figure H).

Figure H
Click for larger image

Select your mail profile.


Note
If for some reason, you do not see the profile you created in the dropdown menu, you have configured your Outlook mail settings incorrectly. Please go back and check your work for something that you may have missed.


Click Test to verify that your settings work correctly (Figure I). The test starts and stops the MAPI profile you created.

Figure I

Take advantage of the Test feature to check your profile.

Our next test is to create a SQL Server Operator and test the SQL Mail functionality. To create an Operator, expand SQL Server | Management | SQL Server Agent | Operators and highlight Operators, as shown in Figure J.

Figure J

Here's where you set up a SQL Server operator.

Next, right-click on Operators and choose New Operator (Figure K).

Figure K

Create a new operator.

Now, type the name of the operator and the operator's e-mail address for SQL Mail to use. Next, click Test to send a test e-mail (Figure L). A dialog box will display a message that you have sent your e-mail successfully.

Figure L

Run a test on sending e-mail from SQL Mail.

Your next step is to open up Outlook and check your e-mail to see that you are receiving these messages, as shown in Figure M.

Figure M

Go to your e-mail client to verify that the SQL Mail messages are being delivered.

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 N shows how this would break out.

Figure N

Using xp_sendmail allows you to mail SQL Server information.

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.

Summary

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.

» See All Articles by Columnist Steven S. Warren



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















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