Generic SQL action templates for e-mail

Note: To use the sp_send_dbmail, you must first configure a default e-mail profile or specify an explicit profile. Refer to the SQL Server documentation for specific steps on how to configure each one. If an e-mail message is not sent as expected, check the SQL Server Log for possible errors.

Users can use the following queries to get information about whether the e-mail profile is configured.

• To view configured profiles, use this query:

  SELECT * FROM msdb.dbo.sysmail_profile

• To view configured e-mail accounts, use this query:

  SELECT * FROM msdb.dbo.sysmail_account

• To view the link between the profile and the account, use this query:

  SELECT * FROM msdb.dbo.sysmail_profileaccount

• To view the information about the profile (Is Default/Public), use this query:

  SELECT * FROM msdb.dbo.sysmail_principalprofile

To configure a generic SQL statement that sends an e-mail message, select one of the "Send an E-mail message…" options (one with a query and one without) in the list of generic SQL action templates. For example:

master..sp_send_dbmail

@recipients = <ToWhom>,

@body = 'The event @EventTagName occured at @EventTime',

@query = <"Your query">,

@exclude_query_output = <exclude_query_output>

In the second line of the syntax, replace <ToWhom> with the e-mail display name or complete e-mail address of the intended recipient or recipients. Assign the e-mail message to the @message variable. Be sure to enclose the recipient(s) and the message in single quotes. For example:

master..sp_send_dbmail

@recipients = 'John Doe',

@body = 'Check this out',

@query = 'SELECT TagName, DateTime FROM EventHistory

WHERE TagName = "SysStatusEvent"

AND DateTime = (SELECT Max (DateTime)

FROM EventHistory

WHERE TagName = "SysStatusEvent")',

@exclude_query_output = <exclude_query_output>

You must have the Microsoft SQL Server and the SQL Mail component set up properly in order for the sp_send_dbmail extended stored procedure to work correctly.

For more information, see Configure an e-mail action.