SQL Server provides an easy way to email the results of a query to yourself (or to someone else).
To send email with T-SQL, you need to use the sp_send_dbmail stored procedure in the msdb database. This procedure accepts many arguments, one of which is the @query argument. That’s the argument that attaches the results of your query to the email.
To send email using Database Mail in SQL Server, a user must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.
To grant a user access to a Database Mail profile with T-SQL, use the sysmail_add_principalprofile_sp stored procedure in the msdb database.
In SQL Server, Database Mail uses profiles to send the email rather than accounts directly.
Therefore you must first create a profile, and create an account, then add the account to the profile. You also need to grant a user in the msdb database access to the profile.
To add a Database Mail account to a profile with T-SQL, use the sysmail_add_profileaccount_sp stored procedure in the msdb database.
If you’re setting up Database Mail to send emails in SQL Server, you will need to have at least one Database Mail account.
You can create Database Mail accounts with T-SQL by executing the sysmail_add_account_sp stored procedure in the msdb database.
The Database Mail account is the account that sends the email. For example, if you have an automated email that is sent whenever a SQL Server Agent job fails, this is the account that sends that email.
You can also specify things such as a separate “reply-to” email, a display name, etc.
You can have multiple accounts for various tasks if required. Simply execute sysmail_add_account_sp whenever you need to create a new account.
When you use Database Mail in SQL Server to send emails, you can specify a Database Mail profile to send the mail from.
In some cases you will be required to specify a profile when you send the email. This is the case when the user does not have a default private profile and there is no default public profile for the database.
You can create Database Mail profiles with T-SQL by executing the sysmail_add_profile_sp stored procedure in the msdb database.
In SQL Server, you can use the sysmail_help_queue_sp stored procedure on the msdb database to see how many mail items are in the queue, the status of the queue, and when it was last activated.
There are two types of queue: the mail queue, and the status queue.
You can also return account info based on the queue type.
When you use Database Mail to send emails in SQL Server, you can use the sysmail_delete_log_sp stored procedure to delete items from the event log.
You can choose to delete all events, events based on their logged date (i.e. before a certain date) or events based on the event’s type.
SQL Server provides the ability to send emails via its Database Mail solution.
The actual act of sending mail is done with the sp_send_dbmail stored procedure. But before you start sending emails from SQL Server, you need to enable and configure Database Mail.
You can do this with the SSMS GUI, or with T-SQL.
This article demonstrates how to do it with T-SQL. It assumes that Database Mail has never been enabled on your system, and therefore it goes through the steps involved in enabling it.
If you prefer to use the SSMS GUI, see How to Setup Database Mail in SQL Server (SSMS).
When you use Database Mail to send emails from SQL Server, email messages and their attachments are stored in the msdb database. You should periodically delete these messages to prevent the database from growing to large.
To delete these messages with T-SQL, use the sysmail_delete_mailitems_sp stored procedure.
You can delete mail messages based on their send request date (i.e. before a certain date) or based on their status. You can also delete all mail messages by using the current date as the request date.
If you’re using Database Mail in SQL Server, you can change the configuration settings by executing the sysmail_configure_sp stored procedure in the msdb database.