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.
