Query result messages

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.

NoteFor 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"