Configuring and Managing SQL Mail
Perhaps one of the more compelling hands-off administration abilities of SQL Server is the SQL Mail capability. SQL Mail lets you send, receive and process mail from within SQL Server. The most common use of this is setting up the SQL Agent process to alert you to server conditions. Since many wireless devices (phones, pagers, etc.) now support an email address, it’s possible to easily have the server notify you of system problems automatically.
This really supplements your downtime management of the server. Since you can set up alerts and use operators to watch over the system, you can quickly significantly decrease the time required for notification.
SQL Mail is typically used in one of two different ways. First, it’s used as a component of the SQL Agent services, providing the means for operators to be notified by the system.
Secondly, SQL Mail can be used by your applications through the use of extended stored procedures. You can start and stop the services, send and receive mail, etc.
The first thing you’ll need to know is what type of mail server you’ll be using to handle the mail – from there you can set up the different services and options that you need to support your server.
Steps to using SQL Mail:
- Select the mail server you’ll be using – you’ll need to know the type of server (Exchange, SMTP/POP3, etc.), the name of the server and the profile of the user you want to use for the configuration.
- Configure SQL Mail to use that server and the user information you’ve set up for access, and
- Set up SQL Agent operators
- Set up stored procedures that will use the extended stored procedure approach.
Items 3 and 4 are not both required – you will have to determine how you’ll be working with SQL Mail and choose one or both of these options.
SQL Mail Configuration
SQL Mail consists of a small set of items that must be configured to know the accounts and servers that will be used to send and receive mail. It simply consists of a mail service setup on the SQL Server along with SQL Mail configured to use that service.
SQL Mail can be configured to use either Exchange or a simple POP3/SMTP server. Exchange is the most prevalent at configuration for it is much easier, so it is recommended to use Exchange if there is an option
Configuring SQL Mail for Exchange
SQL Mail for Exchange utilizes a mailbox setup on the Exchange server, a profile pointing to that mailbox, an Exchange client, SQL Server services running under named user accounts, and the SQL Mail service within SQL Server.
Most installations will have the services for SQL Server setup under the local system account. This offers the greatest amount of network protection, but it prevents some of the ancillary services such as SQL Mail from working.
The Exchange mailbox is setup for a specific network login and so the local system account can not be used. Instead you must use a named NT account. You still want to protect your network, so it is recommended that you create an account specifically for the use of SQL Mail. This account must have administrator privileges on the SQL Server machine, so it should be added to the local administrator group. However, you should also explicitly revoke any access to other machines or resources on the network for this account. This prevents an unauthorized user from accessing your network using this account.
Setting up SQL Mail is a very straightforward process.
- Log on to NT using the account that has been set up for using SQL Mail.
- Change the NT account for the SQL Server service to match your SQL Mail account
- Stop and start SQL Server
- Install Outlook or an Exchange client. You will want to select the Exchange service and leave all others blank.
- After installation is complete, reboot the machine and log on to NT.
- Have your Exchange administrator create a new mailbox that will be used by SQL Mail.
- On your SQL Server machine, create a new profile for the Exchange service. This profile needs to be setup using the Connect to Network option.
- Start up Outlook or the Exchange client and verify that you can send and receive mail.
- From within Enterprise Manager, right click on the SQL Mail icon and enter the profile name that you just created. Make sure you test the connection and have selected the auto start check box.
- Start SQL Mail. Once you see a green arrow display, SQL Mail is running.
- From within the Query Analyzer, execute xp_sendmail to send a message to the mailbox you created for the SQL Mail account.
- If you start up Outlook or your Exchange client, you should see the test message appear in the inbox.
Configuring SQL Mail for a POP3/SMTP server
Configuring SQL Mail to use a POP3/SMTP server is much more involved, although not more difficult.
To enable this, you will need to install Windows NT Messaging, acquire a POP and SMTP mail account, a mail client, and a named NT account.
Just like in the set up of SQL Mail for Exchange, you will need to use a named SQL Server account. This account should follow all of the restrictions and requirements noted in the section above.
To set up SQL Mail for a POP3/SMTP server, follow these steps:
- Close all programs and login to the SQL Server machine using the NT account you have selected for SQL Mail.
- Install Windows NT Messaging
- Right click the Inbox, select properties, and add a new profile.
- Create the new profile with only Internet Mail as an option and connecting through the network
- Specify the name of the mail server and an automatic transfer method.
- Specify your email address
- Enter the NT account name in the Full Name box.
- In the mailbox name box, enter the login name for the mail server.
- The profile should be setup to use the default address book and folder settings.
- After finishing the creation of this profile, select this profile name to be used when Windows messaging starts up.
- Stop the SQL Server and SQL Executive services
- Change the NT account name to the account you are using for SQL Mail
- Start up the MAPI compliant mail client you will be using.
- Create a new connection for your mail server.
- Specify the name of the account, POP3 server name, SMTP server name, e-mail account, and password.
- Make sure it is configured to use a LAN.
- Test the client setup to make sure mail can be sent and received.
- From within Enterprise Manager, right click on the SQL Mail icon and enter the profile name that you just created. Make sure you test the connection and have selected the auto start check box.
- Start SQL Mail. Once you see a green arrow display, SQL Mail is running.
- From within the Query Analyzer, execute xp_sendmail to send a message to the mailbox you created for the SQL Mail account.
- You should see the test message appear in the inbox.
Setting Up Operators
Once configured, SQL Mail is the foundation for your operator notifications as well as mentioned earlier. To set up operator notifications, in Enterprise Manager open the Management, SQL Server Agent object tee.
Right-click on the Operators object, then select New Operator. See the figure below.
Setting up the operator lets you indicate not only the email address (or distribution list) to use, but also the pager information.
You also need to indicate what types of things the operator will be notified for. As an example, you can select which operators are responsible for different types of errors. In the figure below, you can see that this operator has been selected to be notified for tempdb full errors.
By carefully assigning specific conditions to individual operators, you can control who will respond to a specific condition – using the operator’s strengths (and system rights) to your advantage.
You set up the alerts and tasks that can be responded to in the SQL Agent Alerts and Tasks object trees. As you do, you have the opportunity to select the responding operator (from those already configured on the server) from the task and job setup dialogs as well as the operator dialogs shown above. Select the Response tab from the New Alert Properties dialog to indicate the operator that should handle the alert condition.
One interesting aspect of setting up the alert notification here instead of in the operator dialog is that you can also add another message to the notification. For example, you may want to add in the phone number of the operator’s supervisor in case they get hung up on an issue and need to contact someone for additional help.
These same types of notification abilities are available at the Job level. When you are setting up a job, select the Notifications tab from the New Job Properties dialog. You can pick the responding operators and select the conditions under which that operator should be notified. See the figure below for an example.
You can control the specific conditions under which an operator will be notified of problems with jobs.
Extended Stored Procedures for Mail Processing
If you’re building a system where you’ll be calling and using the SQL Mail abilities directly, you’ll be using the extended stored procedures for SQL mail. There are seven extended stored procedures that can be used to manage e-mail from within SQL Server and are found in the master database.
The stored procedures shown in the next table are available, each is described with its respective parameters following the table.
| Stored Procedure | Purpose |
| Xp_startmail | Starts SQL Mail – use this procedure to manually start the mail process to send mail within a stored procedure or other routine. Once started, the mail process will be live system-wide, not just in the single routine calling the xp_startmail procedure.
If you’ve set up the mail process to start automatically when SQL Server starts, you won’t need to call this procedure. You set up the process to start automatically from the Support Services option in the Enterprise Manager. |
| Xp_stopmail | Stops SQL Mail – xp_stopmail will cease mail operations system-wide. To once again send mail, you must re-start the service.
If you have SQL Mail set to start automatically when SQL Server starts, it will re-start when you start SQL Server. Until that time, the mail services will be unavailable. |
| Xp_sendmail | Sends a message using the parameters (destination, source, SQL statement, etc.) that you indicate. |
| Xp_findnextmsg | Locates the next message in the SQL Mail mailbox so you can further process the mail, letting you create a batch-oriented mail system. |
| Xp_readmail | Reads a message in the SQL Mail mailbox, letting you access the headers and content of the mail message. |
| Xp_deletemail | Deletes a message from the inbox. |
| Xp_proccessmail | Executes xp_findnextmsg, xp_readmail, xp_deletemail, and xp_sendmail to process all mail messages in the SQL Mail mailbox |
Using XP_StartMail
Xp_startmail is used to start the mail services in SQL Server if they have not already been started, either by another call to this procedure or by setting the automatic start option in the SQL Mail properties. The syntax for startmail is:
xp_startmail [[@user =] 'user'] [,[@password =] ,password']
The user name and password for the mailbox can be specified, but this is not necessary unless you did not specify a password for the profile that is being used when you set up the SQL Mail properties.
Using XP_StopMail
Xp_stopmail stops all mail processes on the server. This will shut down the connection to the mail server and subsequent calls to the mail extended stored procedures will fail until the service is restarted. Xp_stopmail is very simple, with no parameters:
xp_stopmail
Using XP_SendMail
The xp_sendmail procedure is the heart of the system if you’re creating an outbound messaging system, probably the most common application for SQL Mail. Sendmail lets you indicate where you want to send the message, what the contents are, the attachments, etc. All of the attributes to email that you’re probably used to can be indicated in this call.
xp_sendmail {[@recipients =] 'recipients [;…n]'}
[,[@message =] 'message']
[,[@query =] 'query']
[,[@attachments =] attachments]
[,[@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]The parameters are outlined in the table below.
Parameter Description @recipients This is a semicolon separated list of recipients that the message will be sent to. @message The message that will be sent. Note that it can only be 8,000 bytes long. @query Any valid query to be executed and attached to the mail message. It can not reference special #Inserted and #Deleted tables. @attachments A file to attach to the message. Note that this file path is on the server, not the client system. @subject The subject of the message. @attach_results By default, the results of the query are simply placed in the body of the message. If instead you want them to be added as an attachment, set this to true. This can be handy if your query is particularly large – it can help avoid wrapping. Remember, you don’t control the recipients mail system and how they view the mail message, but attachments generally come through as sent. @no_header Suppresses the column headers for a query. @set_user The security context the query should be run under. The default is guest. There is an apparent bug with using this option. Any user name that is specified returns an error message stating that user does not exist. Also even if the guest account is not enabled, a query will still execute. This query is executed under the sa account. @dbuser The database context to run the query in. You should always specify the database.
Using XP_FindNextMSG
FindNextMSG lets you move through the inbox, seeing the messages that are available. It has an Unread_ONLY flag that you can set, making it easier to process only pending messages, leaving others in the inbox.
xp_findnextmsg [[@msg_id =] 'message_number' [OUTPUT]]
[,[@type =] type]
[,[@unread_only =] 'unread_value'])The parameters are outlined below:
Parameter Description @msg_id ID of the next message @unread_only Restricts the search to only unread messages. Using XP_ReadMail
When you’ve found the message you want to read, you can use the ReadMail stored procedure to pull in the contents and control how it is processed. There are a number of parameters to this call, each controlling different aspects of how the message is provided to the SQL process. The syntax is shown below, the parameters are outlined in the table following the listing.
xp_readmail [[@msg_id =] 'message_number'] [, [@type =] 'type' [OUTPUT]]
[,[@peek =] 'peek']
[,[@suppress_attach =] 'suppress_attach']
[,[@originator =] 'sender' OUTPUT]
[,[@subject =] 'subject' OUTPUT]
[,[@message =] 'message' OUTPUT]
[,[@recipients =] 'recipients [;…n]' OUTPUT]
[,[@cc_list =] 'copy_recipients [;…n]' OUTPUT]
[,[@bcc_list =] 'blind_copy_recipients [;…n]' OUTPUT]
[,[@date_received =] 'date' OUTPUT]
[,[@unread =] 'unread_value' OUTPUT]
[,[@attachments =] 'attachments [;…n]' OUTPUT])
[,[@skip_bytes =] bytes_to_skip OUTPUT]
[,[@msg_length =] length_in_bytes OUTPUT]
[,[@originator_address =] 'sender_address' OUTPUT]]
Parameter Description @msg_id ID of the next message @originator The returned email address of the user sending the message. It can only be 255 characters. @subject The subject of the message. Can only be 255 characters. @message This is the text of the mail message which is normally a query to execute. This variable is only capable of handling 8000 characters. @recipients This is a semicolon separated list of recipients @skip_bytes This is a hold over from version 6.5 which was used to read large messages in chunks. It specifies the number of bytes to skip when reading the mail message. @msg_length Used in conjunction with @skip_bytes to sequentially process 255 byte chunks of a message. Using XP_DeleteMailWhen you a message you need to remove from the inbox, the deletemail procedure is what you’ll use. Note that you must have already determined the message_number – you can retrieve this information with FindNextMsg or ReadMail. Probably the most common use is to use ReadMail to retrieve a message, do the processing needed and then use deletemail to remove the message from the inbox. The syntax for DeleteMail is:
xp_deletemail {'message_number'}
Using XP_ProcessMail
XP_ProcessMail is a very powerful tool. You can use ProcessMail as an automated engine that will query the inbox, look for messages that you indicate, then run the queries contained in the message against the database.
ProcessMail will search the inbox for messages that you indicate and will read them in. It will then use the SQL statement in the message body, run it against the database you indicate and return the results to the sender automatically.
You indicate several parameters in the command syntax that let you control the types of messages that ProcessMail will work with. The syntax for ProcessMail is:
sp_processmail [[@subject =] 'subject']
[,[@filetype =] 'filetype']
[,[@separator =] 'separator']
[,[@set_user =] 'user']
[,[@dbuse =] 'dbname']The next table shows how these parameters control the operation of ProcessMail.
Parameter Description @subject When you indicate a subject, only those messages with the subject you call out are used. This would be a good way to have multiple ProcessMail commands looking at your inbox and selecting only those commands that pertained to the database they had. Tip: as a generic query engine, you could set up one ProcessMail task for each database. Have it look at the subject line for its database and process only those messages for which it was defined. From there, the SQL statement in the body of the message can be used to SELECT the table and contents needed to be returned. By doing this, you set up a generic query engine, driven by the subject line.
@filetype If you want to return the results to the user with a file extension other than TXT (text file), you can set this option. For example, if you want the results to be associated with Word, you could assign a DOC extension. @separator The separator is used to indicate what character will be placed between the columns returned to the user. @set_user The security context the query should be run under. The default is guest. @dbuse The database that should be used to run the query against. Always indicate the database as it defaults to MASTER. SQL Mail Frequently Asked Questions
SQL Mail is very simple to set up and run in your environment. It also adds a very powerful notification mechanism. It is not all roses though, and a few issues have arisen with SQL Mail.
The most notable examples are below:
- Mail messages accumulate in the outbox of the mail client instead of being sent immediately.
This is generally seen when using SQL Mail in conjunction with Exchange. The Outlook and Exchange client have the capability of utilizing offline folders. You must disable offline folders in order for messages to be sent immediately.
- Mail messages can not be sent.
This is normally not due to SQL Mail, but resides within the mail system. Verifying your mail client set up and resolving any delivery problems will usually alleviate this. In very rare instances the mail client functions correctly and all settings are configured correctly. This problem is usually fixed by uninstalling the mail client, reinstalling, and reconfiguring the profiles.
浙公网安备 33010602011771号