This topic describes how to use DBMail in various scenarios. To avail the functionality, database mail must be configured in your environment.
1) Send a test email from SQL Server using DBMail.
2) Send a test email to multiple recipients in “TO” section.
3) Send a test email to different recipients in different fields (To, CC and BCC)
4) Send a mail in a simple text format based on a simple query
5) Email the content of a variable
6) Email the query results as an attachment
7) Email the query results as an attachment – providing the database name in which the query needs to be executed.
8) Send a mail in a simple text format based on a query with clauses.
9) Send an email with a conditional clause.
10) Send the query results in a HTML tabular format.
11) Send the query results in a Stylish HTML & CSS format
Let’s see the syntax as per the documentation.
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 ]
There are a lot of input parameters that aid in different situations. For eg, to easily check if the database mail is running, or to check if the database has been restored with the new data, to submit a report without using SSRS, etc. These scenarios, which are often used, are listed here.
1) Sending a test email from SQL Server
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’Mail_Profile’,
@recipients=’contact@bigdatansql.com’,
@subject=’Test message’,
@body=’This is the body of the test message.’
Explanation: The stored procedure sp_send_dbmail is in the MSDB database. The above code will help in sending a test email.
@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 is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max).
@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 is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.
2) Sending an email to multiple recipients
Use msdb
GO
EXEC sp_send_dbmail @profile_name=’Mail_Profile’,
@recipients=’admin@bigdatansql.com; contact@bigdatansql.com’,
@subject=’Test message’,
@body=’This is the body of the test message.’
Explanation: The email addresses must be separated by semicolon (“;“).
3) Send a test email to different recipients in different fields (To, CC and BCC)
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’Mail_Profile’,
@recipients=’admin@bigdatansql.com; contact@bigdatansql.com’,
@copy_recipients = ‘shaikshafi.nabi@gmail.com’,
@blind_copy_recipients = ‘shaikshafi.nabi@gmail.com’
@subject=’Test message’,
@body=’This is the body of the test message.’
Explanation:
@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 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.
4) Send a mail in a simple text format based on a simple query
USE msdb
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail_Profile’,
@recipients = ‘contact@bigdatansql.com’,
@query = ‘SELECT EmpNo, Ename, DeptNo FROM TestDB.dbo.Emp’,
@body = ‘ Employee Records ‘,
@subject = ‘Employee Records’,
@body_format = ‘TEXT’
Explanation:
@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.
5) Email the content of a variable
USE msdb
GO
DECLARE @iCount INT
SELECT @iCount = COUNT(*) FROM TestDB.dbo.Emp
EXEC msdb.dbo.sp_send_dbmail
@recipients=N’contact@bigdatansql.com’,
@body= @iCount,
@sensitivity =’Personal’,
@importance =’High’,
@copy_recipients =’admin@bigdatansql.com’,
@subject =’Record Count’,
@profile_name =’Mail_Profile’;
Explanation:
The content of a variable can be passed into the body of the e-mail message as shown above..
6) Email the query results as an attachment
USE msdb
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ‘ SELECT TOP 5 [EmpNo],[Ename],[Sal]
FROM [TestDB].[dbo].[Emp] a WITH (NOLOCK)’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail_Profile’,
@recipients = ‘contact@bigdatansql’,
@query=@SQL,
@subject=’Top 5 Employee Records’,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘EmpRecords.csv’,
@query_result_width =3767,
@query_result_no_padding=1,
@query_result_header = 0
Explanation:
No separation / delimiter is provided explicitly to separate the columns. By default a space is allocated.
Below is the result.
EmpNo Ename Sal
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
(5 rows affected)
If you see, the query has 3 part names while invoking the table. The database name has been provided. This can be rewritten by providing the database name as a input parameter.
7) Providing the database name in which the query needs to be executed.
USE msdb
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ‘ SELECT TOP 5 [EmpNo],[Ename],[Sal]
FROM [TestDB].[dbo].[Emp] a WITH (NOLOCK)’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail_Profile’,
@recipients = ‘contact@bigdatansql’,
@query=@SQL,
@subject=’Top 5 Employee Records’,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘EmpRecords.csv’,
@query_result_separator=’,’,
@execute_query_database = ‘TestDB’,
@query_result_width =3767,
@query_result_no_padding=1,
@query_result_header = 0
Explanation:
Query result separator is comma (,) in the above example.
Click here for the next part
Big Data and SQL 
5 comments