How to configure SQL Server to send e-mail

Server
How to configure SQL Server to send e-mail

Zhu Yi (2004.9 reprint, please specify the author)


As you normally know, SQL Server is well integrated with Microsoft Exchange Server, and there are detailed instructions for configuring this in SQL Server online Help, no longer repeating. However, the question we are more concerned with is: How do you configure SQL Server to send mail using an Internet Mail server without Exchange Server?
The author has for this problem to hurt the brain, searched the relevant information on the Internet, found that only a few of the data have been a stroke, and some although introduced the operation steps, can follow the steps of step-by-step operation down, the result is always failure. To this end the author repeatedly experiment, finally found a simple and effective method, dare not enjoy alone, the following is a detailed configuration steps, if you encounter any problems in the process, please leave a message.

Test environment:

1 Windows Watts Server
2 SQL Server 2000+SP3
3 Microsoft Outlook (Office 2000)

To prepare an Internet mail account:
To test your e-mail account: test@163.com
SMTP Server smtp.163.com
POP3 server pop.163.com

Step One: Change the e-mail configuration

1 Open Microsoft Outlook Click Options on the Tools menu, and then click the Mail Services tab.
2 Click "Reconfigure Mail support."
3 Select the For Enterprise or Workgroup option
4 Restart Microsoft Outlook

Step Two: Add a configuration file

1 Click Services on the Tools menu, and then click the Add button.
2 Select Internet Email and click OK.
3 The following steps are to set up your e-mail account, refer to the Microsoft Outlook Help file, and do not repeat it here.
4 Use the configured email account to send and receive mail to confirm that the configuration was successful.

Step three: Configure the MSSQLServer service to use the Mail configuration file

1 Restart the MSSQLServer service (required, otherwise the MSSQLServer service does not detect the configuration file that was added in the previous step)
2 Open Enterprise Manager-> expand Server-> Support Services-> right click the "Mail"-> click the dropdown box, you will find
Microsoft Outlook Internet Settings option, click Test. If you pop up a dialog box that says success, congratulations, you're done.

Step four: Enjoy the fruits of your labor

1 Query Analyzer to open SQL Server

2 Enter the following SQL statement to execute
EXEC master.. xp_sendmail ' test@163.com ', ' hello,world! '

3 All right, go to your email inbox.


Note: The above configuration process requires that you log on to Windows Server with the Windows account used by the MSSQLServer service, that is, the account you use to log on to the computer is the same as the Windows account used by the MSSQLServer service.