Outgoing Messages

An outgoing message can consist of text or the formatted results of a query or batch of queries. You can send a message directly through isql from either a stored procedure or a trigger that uses xp_sendmail.

To send query results, input the query, or a stored procedure containing the query, to xp_sendmail. The query results are sent to message recipients.
When the message consists of query results, you can send the results in the body of the e-mail message or as an attachment.
When the message consists of text, use the message parameter to xp_sendmail.
When the message consists of query results use the query parameter, and pass the quoted text of the query or the quoted execute command with its stored procedure name.

For syntax and parameters for xp_sendmail, see the Reference Manual: Procedure.

Text Messages

This example shows how you can use a trigger to send an e-mail to "purchasing" when an update causes the number of items available (onhand) in an inventory table (part) to fall below a certain level (min_onhand).

1> create trigger reorder
2> on part
3> for update as
4> if update(onhand)
5> if (select onhand - min_onhand 
6> from inserted <= 0
7> begin
8> execute xp_sendmail
9> @subject="Inventory Notice"
10> @recipient="purchasing"
11> @message="Parts need to be reordered."
12> end

Query Result Messages

The purchasing department can send the Adaptive Server mailbox a query to determine which parts should be reordered.

Adaptive Server then reads the query into a variable, named received_mess, and uses xp_sendmail to execute it and return the results:

declare @received_mess varchar(255)
execute xp_sendmail @recipient = "purchasing"
@query = @received_mess, @dbname = "inventory"
@dbuser ="sa"

Another example of mailing query results, a user-defined stored procedure, named usp_salesreport, in the salesdb database, is run at the end of the month to report on monthly sales activity. By invoking this procedure inside a call to xp_sendmail, you can automatically send the results of the procedure to a mail group.

This example sends the results of the usp_salesreport stored procedure as an attachment to an e-mail message addressed to “sales”, with copies to “mitchell” and “hasani”. The procedure is executed in the salesdb database with the privileges of the database owner of salesdb.

execute xp_sendmail @recipient = "sales",
@copy_recipient = "mitchell"; "hasani",
@subject = "Monthly Sales Report",
@query = "execute usp_salesreport",
@attach_result = true,
@dbname = "salesdb",
@dbuser = "dbo"