DBMAIL

DBMAIL

Terminology:

Account: Email Account (Not SQL Server User) having access to SMTP servers. Multiple accounts are only required if more than one SMTP server is present or to use more than one sender’s email address.

Profile: Set of Email Accounts.

Note: A profile can consist of multiple accounts. An account can be member of different profiles. This is a many to many relationship.

Users: SQL Server Users who directly or through group membership are in DatabaseMailUserRole in msdb to send emails. Access to Users is provided through profiles. For Public profile all users in msdb and member of DatabaseMailUserRole in can send mail. For Private Profile, sysadmin need to provide access to sql servers.

Prerequisites:

1. SMTP server
2. Enable DBMAIL ( Facets (SQL 2008) or Surface Area Configuration (SQL 2005 )

3. Email account or set of email accounts having access to SMTP

4. SQL Server Service Account should have access to SMTP Server. So this should be a domain account.

5. Database Server should have access to SMTP. SMTP relay for the system should be enabled at SMTP (Exchange) server.
6. SQL Server Users who needs to send email must be a member of DatabaseMailUserRole in msdb and should have execute permission to sp_send_dbmail

7. For Private Profiles , sysadmin should provide access to users in msdb having DatabaseMailUserRole in msdb
8. For Public Profile , All users in DatabaseMailUserRole in msdb can send the mail

Introduction



1. Database Mail can be used for sending e-mail messages to users from the SQL Server Database Engine. The messages can contain query results/include files from any resource on the network.
2. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. This does not require any POP3 client like outlook to be installed on SQL Server which is the case for SQL MAIL.

3. To minimize the impact on SQL Server, the component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.

4. A Database Mail profile allows specifying more than one SMTP server. If an SMTP server is unavailable, mail can still be delivered to another SMTP server

5. Database Mail is fully supported on a cluster

6. Database Mail provides background, or asynchronous, delivery. When sp_send_dbmail is called to send a message, Database Mail adds a request to a Service Broker queue. The stored procedure returns immediately. The external e-mail component receives the request and delivers the e-mail.

7. Database Mail allows creating multiple profiles within a SQL Server instance. Optionally, a profile can be chosen to send a message.
8. Each profile can contain multiple failover accounts. Different profiles can be configured with different accounts to distribute e-mail across multiple e-mail servers

9. Database Mail is fully supported on 64-bit installations of SQL Servers

10. To send Database Mail, user must be a member of the DatabaseMailUserRole database role in the msdb database.

11. A Public profile consists of all users in msdb database users or groups.

12. A private profile restricts access to a specified list of users.

13. Users can figure Attachment file size and attached file extensions with sysmail_configure_sp

14. Database Mail maintains the information for e-mail accounts within SQL Server Database Engine. This can be configured through Database Mail Configuration Wizard or T-SQL.

15. Database Mail logs e-mail activity to SQL Server, the Microsoft Windows application event log, and to tables in the msdb database.

16. Database Mail keeps copies of messages and attachments sent in the msdb database for auditing Database Mail usage and review the retained messages

17. Database Mail allows e-mails formatted as HTML
18. Database Mail can be enabled By

• Database Mail Configuration Wizard

• sp_configure

• surface area configuration

19. Sysadmin can only use the dbmail wizard. Users in DatabaseMailUserRole database role in msdb can only send messages 

DB Mail Architecture



1. Configuration and security objects create profiles and accounts used by Database Mail in msdb

2. The msdb database acts as the mail-host database that holds the messaging objects those Database Mail users to send e-mail. These objects include the sp_send_dbmail stored procedure and the data structures that hold information about messages.

3. msdb size need to be controlled periodically since this database contains all the email sent with attachments. Daily backup of this database is very important.

4. The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers

5. Database Mail records logging information in the msdb database and the Microsoft Windows application event log.

Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail90.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.


Database Mail provides two types of configuration objects:

• Database Mail profiles are sets of accounts.

• Database Mail accounts contain information for e-mail servers.

 
sp_send_dbmail

Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

Transact-SQL Syntax Conventions

Syntax

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]

[ , [ @recipients = ] 'recipients [ ; ...n ]' ]

[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]

[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]

[ , [ @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 ]

[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Arguments

[ @profile_name = ] 'profile_name'

Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

[ @recipients = ] 'recipients'

Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @copy_recipients = ] 'copy_recipients'

Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @blind_copy_recipients = ] 'blind_copy_recipients'

Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @subject = ] 'subject'

Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

[ @body = ] 'body'

Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.

[ @body_format = ] 'body_format'

Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

• TEXT

• HTML

Defaults to TEXT.

[ @importance = ] 'importance'

Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:

• Low

• Normal

• High

Defaults to Normal.

[ @sensitivity = ] 'sensitivity'

Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:

• Normal

• Personal

• Private

• Confidential

Defaults to Normal.

[ @file_attachments = ] 'file_attachments'

Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max).

[ @query = ] 'query'

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

[ @execute_query_database = ] 'execute_query_database'

Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file = ] attach_query_result_as_file

Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename = ] query_attachment_filename

Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header = ] query_result_header

Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

[ @query_result_width = ] query_result_width

Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

[ @query_result_separator = ] 'query_result_separator'

Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

[ @exclude_query_output = ] exclude_query_output

Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.

[ @append_query_error = ] append_query_error

Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error is bit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.

[ @query_no_truncate = ] query_no_truncate

Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

Note:

When used with large amounts of data, the @query_no_truncate option consumes additional resources and can slow server performance.

[ @mailitem_id = ] mailitem_id [ OUTPUT ]

Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.

Return Code Values