MS SQL Basics Tutorial: SQL Server Tools-sql Mail

In addition to providing powerful management tools Enterprise Manager, reliable security management and database backup and recovery, and built-in replication components, SQL Server includes a number of other tools and reassuring features. In this chapter we'll introduce SQL Mail, SQL Server Profiler, Query Analyzer, and hopefully readers will be able to use them skillfully to create a comprehensive understanding of SQL Server.

Introduction to 19.1.1 SQL Mail

In the SQL Server Agent Chapter We have already said that when an alert created by a user is executed, the alert notifies the operator by e-mail or pager. Because an alert is always based on an event (an alert is executed when an event occurs), if the database is compromised or conditional (when the condition exceeds a predetermined value, such as a performance alert), the administrator will understand which event or state the system is currently working on when receiving an e-mail or pager message. Here the email or pager message is delivered to the operator with the help of the SQL Server Agent's own mail characteristics, which we call SQLAgentMail. SQLAgentMail and SQL Mail are the two different services that SQL Server provides for delivering mail. However, the difference between the two is that SQL Mai provides more powerful Mail functionality.

If a SQL Server server or client connects to a mail server, SQL Server can receive or send e-mail on any server with a MAPI-compliant messaging system with the help of SQL Mail. Not only that, because SQL Mail provides a number of system processes that make it easier for users to use these stored procedures in their own applications, stored procedures, or triggers, this completely breaks through the limitations of SQLAgentMail message delivery based on event and condition.

Before you let SQL Mail express its superiority, you must first configure SQL Mail correctly, otherwise it will be impossible to talk about it.

19.1.2 Configuration SQL Mail

This is an easy thing to do simply by configuring SQL Mail, but there are a few important things you need to do before configuring SQL Mail.

Because SQL Mail can interact with any MAPI-compliant messaging system, it is not difficult to select MAPI compliant software, either by using Microsoft Mail or by using Microsoft Exchange. Here we recommend that you use Microsoft Exchange.

Next, in order for SQL Server to receive or send messages, you must set up a Microsoft Exchange mailbox for SQL Server that is created by Microsoft Exchange administrators through Microsoft Exchange Administrator, so as a SQL Server system administrator, you should establish a good relationship with your Microsoft Exchange Server Administrator.

Then, to set up SQL Server as a mail client, you must install and configure Outlook client software on the client running SQL Server. Configuring Outlook client software is done by mail and fax on the control Panel.

Finally, test that the Exchange connection is valid and, if it fails, reconfigure it.

NOTE: Because SQL Mail runs under the SQL Server service login account, you must ensure that the NT login account is the same as the SQL Server service login account and is an NT system administrator.

When the above preparation is complete, you can configure SQL Mail to perform the following steps:

(1) Start SQL Server Enterprise Manager, log on to the specified server, and open the Support Service folder.

(2) Right-click the SQL Mail icon to open the SQL Mail Configuration dialog box, as shown in Figure 19-1.

Select the mail profile that you created in the Drop-down list next to the profile, and then click the Test button to test. If the configuration succeeds, the Configuration success message box pops up.

19.1.3 Using SQL Mail

SQL Mail includes many stored procedures that enable SQL Server to receive, send, and process messages, and use these stored procedures to execute queries and return query results to recipients, or to respond to e-mail messages that contain queries or stored procedures. These SQL Mail-related stored procedures and their implications are shown in table 19-1.

See the full set of "MS SQL Basics Tutorials"