Tech Note 551
Configuring SQLMail or Database Mail for the Historian Event
System
All Tech Notes, Tech Alerts and KBCD documents and software are provided "as is" without warranty of any kind. See the Terms of Use for more information.
Topic#: 002298
Created: September 2008 Updated: February 2009
Introduction
This Tech Note provides procedures for configuring either SQLMail or Database Mail on SQL Server 2005. The InSQL Event System supports both. The examples are based on a Microsoft Exchange Server Mail account.
Note: This Tech Note is intended to provide helpful suggestions, and its content is NOT supported by Wonderware. Wonderware is not responsible for Microsoft's SQL mail and
Database Mail configuration. If any issues are encountered, please contact Microsoft Support.
Application Versions
IndustrialSQL Server Historian 9.0 and later Microsoft SQL Server 2005
Configuring SQLMail
SQLMail is the legacy mailing system for Microsoft SQL Server.
The command that is used to send mail by the IndustrialSQL Server Event System is
xp_sendmail. SQL Server 2005 by default has disabled these commands so you must enable them.
Note: You can copy/paste all the SQL statements included in this Tech Note from your browser.
Enabling SQLMail
To enable xp_sendmail, run the following SQL statement.
use master
go
sp_configure'show advanced options',1 go
8/22/13 Configuring SQLMail or Database Mail for the Historian Event System
Configure a Default Mail Profile
Now that you've enabled SQLMail, configure a default mail profile for the SQL Server Machine. 1. Go to Control Panel and select Mail.
2. Select Email Accounts.
3. Configure the Exchange Server Account that you will use to send email:
FIGURE 1: CONFIGURE E-MAIL ACCOUNT SETTINGS
To send email using the Exchange Server account, you must have the SQLServer (MSSQLServer) service log in as the user that is used in the mail profile.
4. Go to Services and change the logon user for SQL Server (MSSQLServer).
FIGURE 2: CONFIGURETHE LOG ONFOR MSSQLSERVER
Test the SQLMail Configuration
1. Prior to configuring an event, test the SQLMail configuration by running the following command using SQL Server Management Studio:
exec xp_startmail
exec xp_sendmail @recipients="[email protected]", @subject="Email Test",
@message = "This is a test"
8/22/13 Configuring SQLMail or Database Mail for the Historian Event System
FIGURE 4: CONFIGUREAN EMAILFROMTHE INSQL EVENT SYSTEM
Using DatabaseMail for Event System Emails
Database Mail is the new mailing system for SQL Server. The command that sends mail by the InSQL Event System is xp_sendmail.
The DatabaseMail stored procedure that sends email is sp_send_dbmail. The following steps describe how to use DatabaseMail with the InSQL Event System.
Enabling Database Mail
SQL Server 2005 by default has disabled these commands. To enable sp_send_dbmail, run the SQL statement below:
use master
go
sp_configure'show advanced options',1 go
reconfigure with override
go
sp_configure 'Database Mail XPs',1 go
reconfigure
go
Configuring Database Mail
Configure Database Mail from SQL Server Management Studio.
FIGURE 5: CONFIGURE DATABASEMAIL
8/22/13 Configuring SQLMail or Database Mail for the Historian Event System
FIGURE 6: SET UP DATABASE MAIL
3. Type a profile name (i.e. InSQLMail). You will need to remember this profile name as it will be used later.
FIGURE 7: TYPEA PROFILE NAME
4. Click the Add button to add an SMTP account.
6.
FIGURE 8: TYPEINTHE NECESSARY EMAIL ACCOUNT INFORMATION
7. Click Next to proceed to the Profile Security Section.
8. Select the Private Profiles tab and change the user name to NT Authority\SYSTEM. This is the user that will be allowed to execute sp_send_dbmail.
8/22/13 Configuring SQLMail or Database Mail for the Historian Event System
FIGURE 9: CHANGE USER NAMEAND SELECTTHE ACCOUNT PROFILE
10. Click Next twice, then Finish to complete the wizard.
Test Your Database Mail Configuration
1. To begin testing the Database Mail configuration, from management studio, right-click Database Mail and click Send a Test E-Mail.
FIGURE 10: SEND TEST DATABASE MAIL
InSQL.
4. Instead of using an Email Action Type, use Generic SQL with the following query (in the Action Query field):
EXEC msdb.dbo.sp_send_dbmail @profile_name="InSQLMail", @recipients="[email protected]",
@subject="Database Mail Test", @body="This is a test"
Note: You must restart the InSQLEventSystem Service after making any changes to your event tags. Changes to Generic SQL Statements will not take affect until the service is restarted.
Click the following icon to view this file in .pdf format:
C. Azer
Tech Notes are published occasionally by Wonderware Technical Support. Publisher: Invensys Systems, Inc.,
26561 Rancho Parkway South, Lake Forest, CA 92630. There is also technical information on our software products at Wonderware Technical Support.
For technical support questions, send an e-mail to [email protected].
Back to top
©2013 Invensys Systems, Inc. All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying,
