Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.
Database Mail has two configuration objects: The database configuration objects provide a way for you to configure the settings that Database mail should use when sending an email from your database application or SQL Server Agent.
Database Mail accounts
A Database Mail account contains the information that Microsoft SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.
Creates a new Database Mail account holding information about an SMTP account.
sysmail_add_account_sp [ @account_name = ] 'account_name',
[ @email_address = ] 'email_address' ,
[ [ @display_name = ] 'display_name' , ]
[ [ @replyto_address = ] 'replyto_address' , ]
[ [ @description = ] 'description' , ]
[ @mailserver_name = ] 'server_name'
[ , [ @mailserver_type = ] 'server_type' ]
[ , [ @port = ] port_number ]
[ , [ @username = ] 'username' ]
[ , [ @password = ] 'password' ]
[ , [ @use_default_credentials = ] use_default_credentials ]
[ , [ @enable_ssl = ] enable_ssl ]
[ , [ @account_id = ] account_id OUTPUT ]
Database Mail profiles
A Database Mail profile is an ordered collection of related Database Mail accounts. Applications that send e-mail using Database Mail specify profiles, instead of using accounts directly.
Profiles also help database administrators control access to e-mail. Membership in the DatabaseMailUserRole is required to send Database Mail.
A profile may be public or private.
Creates a new Database Mail profile.
sysmail_add_profile_sp [ @profile_name = ] 'profile_name'
[ , [ @description = ] 'description' ]
[ , [ @profile_id = ] new_profile_id OUTPUT ]
Adds a Database Mail account to a Database Mail profile
Execute sysmail_add_profileaccount_sp after a Database Account is created with sysmail_add_account_sp (Transact-SQL), and a Database Profile is created with sysmail_add_profile_sp (Transact-SQL).
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AdventureWorks Administrator',
@account_name = 'Audit Account',
@sequence_number = 1 ;
How to get Database Config
sysmail_help_configure_sp sysmail_help_principalprofile_sp sysmail_help_profile_sp sysmail_help_profileaccount_sp
How to set database mail Config value
Changes configuration settings for Database Mail. The configuration settings specified with sysmail_configure_sp apply to the entire SQL Server instance.
| Parameter Name | Value | Description |
| AccountRetryAttempts | 1 | Number of retry attempts for a mail server |
| AccountRetryDelay | 60 | Delay between each retry attempt to mail server |
| DatabaseMailExeMinimumLifeTime | 600 | Minimum process lifetime in seconds |
| DefaultAttachmentEncoding | MIME | Default attachment encoding |
| LoggingLevel | 2 | Database Mail logging level: normal – 1, extended – 2 (default), verbose – 3 |
| MaxFileSize | 1000000 | Default maximum file size |
| ProhibitedExtensions | exe,dll,vbs,js | Extensions not allowed in outgoing mails |
sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]
[ , [ @parameter_value = ] 'parameter_value' ]
[ , [ @description = ] 'description' ]
Sends an e-mail message to the specified recipients.
The message may include a query result set, file attachments, or both.
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @from_address = ] 'from_address' ]
[ , [ @reply_to = ] 'reply_to' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @query_result_no_padding = ] @query_result_no_padding ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
How to Enable Database Mail
sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
Some troubleshooting queries
select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_event_log;
SELECT [sysmail_server].[account_id],
[sysmail_account].[name] AS [Account Name],
[servertype],
[servername] AS [SMTP Server Address],
[Port]
FROM [msdb].[dbo].[sysmail_server]
INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];
GO
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
GO
SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'
GO
SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
Mailing Account SMTP Server Name Port Number Gmail smtp.gmail.com 587 Hotmail smtp.live.com 587 Yahoo smtp.mail.yahoo.com 25 AOL smtp.aol.com 587
Reference
