In response to the e-mail message generated by the trigger listed in the previous examples, the purchasing department can send the Adaptive Server mailbox a query to determine which parts should be reordered.
For a diagram of the process, see Figure 9-1.
Adaptive Server then reads the query into a variable, named received_mess, as in the following example, with xp_readmail, 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 through e-mail to a mail group.
The following 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"
