My Baby Done Wrote Me a Letter (Using SQL Mail)
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
E-mail isn't my favorite technology any more. Much of the work and all of the junk I have to plow through comes my way via e-mail. E-mail does have its uses, though, especially when it comes to getting information from SQL Server. In this article, I'll show you how to use e-mail with SQL Server 2000.
SQL Mail System
The SQL Mail system can do four main things:
Send query results to a mailbox
Receive and process mail from a mail system and return query responses to the originating email address
Control a SQL Server using stored procedures and SQL commands
Send messages to mailboxes and e-mail pagers
Alerts, triggers, or stored procedures can be coded to use the SQL Mail system to send a message or the results of a query to a user or multiple users. In this fashion, a mail message can be sent to a system administrator's pager when an alert is raised (alerts), a project manager can receive e-mail notification that a budget value is exceeded (trigger), or a server can e-mail a user that an order was received by a Web site via code (stored procedures).
E-mail messages containing a query in the body can be sent to the mailbox the SQL Mail system logs on to, and when the proper stored procedures are run, the server will respond to that address (with an optional cc list) with the results of the query.
Using T-SQL commands, an administrator can send mail that adds users, changes server parameters, or performs any other administrative task.
All that's needed to make this work is a functional SQL Server (this article targets SQL 2000, but SQL 7 works this way as well) and a MAPI-compliant mail client installed on the SQL Server with a profile defined that the MSSQLServer service can access.
The first thing to do is to make sure that the SQL Server service is started with a user account because the Local System account can't be mapped to a mailbox.
After that's complete, install a MAPI-compliant, e-mail client on the SQL Server and then set up a profile for it. Be sure to use the mailbox tied to the account that's running the MSSQLServer service.
Next, set SQL Server to use that profile for SQL Mail. Here are the steps to do that:
Open Enterprise Manager, drill down to the server's name, and then double-click it.
Expand Support Services, right-click SQL Mail, and then click Properties.
In the Profile name list box, select the proper mail profile. Click Test to make sure that it all works.
Now that everything is set up, use the processes that follow to have the server send a message with the results of a query.
SQL Mail Processes
In order to start any of the SQL Mail processes, a session is required. This is done by either setting SQL Mail using Enterprise Manager or with an extended stored procedure command called xp_startmail. To set up the SQL Mail in Enterprise Manager, drill down to Support Services and then to SQL Mail. Set the profile you want to use by selecting it from the properties of that object.
To use a session-level mail startup, use the syntax for the xp_startmail command displayed in Listing 1.
Listing 1. Starting the Mail Session
USE master GO EXEC xp_startmail 'username', 'password' GO
Replace the username and password variables with the username and password for the applicable mail profile. Leave the single quotes.
After this has been done, other commands process and send mail. Listing 2 shows the commands that run a query, and sends the results to the address buck@hotmail.com.
Listing 2. Mailing Query Results
USE master GO EXEC xp_startmail 'username', 'password' GO USE pubs GO EXEC xp_sendmail 'buck@hotmail.com', @query = 'SELECT * FROM authors' GO EXEC xp_stopmail GO
There are more parameters for this command, such as attaching the results as a text file or setting a more useful subject line. Listing 3 shows these parameters, fresh from Books Online:
Listing 3. xp_sendmail Syntax
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']
Always remember to terminate the session with the command xp_stopmail. It doesn't take any arguments. This is not only good programming form, but if it's left out, SQL can generate errors on the next use of SQL Mail commands.
This same process can perform tasks that are more useful. For example, combining scheduled mails with the results of the stored procedure sp_monitor helps to track a server's status.
SQL Mail can also read e-mails and even respond to what it reads. This process is a bit more involved, but it isn't difficult.
After the session is established, SQL Mail can read the mailbox and interpret the message body of the mail as a query. Only one query can be mailed per message, and the result will be returned to the sender. The commands to do that are shown in Listing 4, and are normally stored as a job that's fired with a trigger or on a schedule.
Listing 4. Processing Mail
USE master GO EXEC xp_startmail 'username', 'password' GO USE master GO EXEC sp_processmail GO EXEC xp_stopmail GO
The sp_processmail stored procedure actually uses other extended procedures (xp_findnextmessage, xp_readmail, xp_sendmail and others) to perform all this magic. The sp_processmail reads the e-mails in an inbox and processes them all. To read one e-mail at a time or e-mail only from a certain user (a good way to provide security), you can use the xp_readmail extended stored procedure.
There are other parameters to this command, such as dictating that only certain subject lines are processed for queries or specifying the database to use. These parameters are shown in Listing 5, again from Books Online.
Listing 5. sp_processmail Parameters
sp_processmail [ [ @subject = ] 'subject' ] [ , [ @filetype = ] 'filetype' ] [ , [ @separator = ] 'separator' ] [ , [ @set_user = ] 'user' ] [ , [ @dbuse = ] 'dbname' ]
Remember to close out each session with the command xp_stopmail.
One word here about security—the e-mail stored procedures are accessible only by the sysadmin and db_owner groups. That's on the server side; unless you delineate certain message headers or originating addresses anyone who sends mail will get a response. That may be a bad thing!
Summary
I hope I've provided some useful information for using SQL Mail to get at the data in your server. I've only scratched the surface here; you can control your server, get information, and do anything else your SQL Server will respond to. Finally, e-mail you want to get!