In our previous post we have discussed about how to configure SQL Server Database Mail. Once the database mail (dbmail) is configured that dbmail profile can be used in TSQL queries or stored procedures to send e-mails to specified recipients. When you successfully issue a sp_send_dbmail command the mail will be queued in the Database Mail queue and returns a mailid value as message. sp_send_dbmail stored procedure is available in the msdb database.
Here is the query to send e-mail messages:
Example 1: Send Plain Text Email Message
DECLARE @MailBody NVARCHAR(MAX), @Subject NVARCHAR(200) SET @Subject = N'Database Mail Status: ' + CONVERT(VARCHAR(20), GETDATE(), 100) SET @MailBody = N'Hello, This is an autogenerated mail from SQL Database Server 01. In case of any concerns please contact sqldb01.mailadmin@organisation.com . Thanks You' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyGmail', @recipients = 'mailrecipient@organisation.com', @subject = @Subject, @body = @MailBody, @importance = 'HIGH';
Example 2: Send Plain Text Email Message with Attachment
DECLARE @MailBody NVARCHAR(MAX), @Subject NVARCHAR(200), @FIlePath NVARCHAR(MAX) SET @Subject = N'Database Mail Status: ' + CONVERT(VARCHAR(20), GETDATE(), 100) SET @MailBody = N'Hello, This is an autogenerated mail from SQL Database Server 01. In case of any concerns please contact sqldb01.mailadmin@organisation.com . Thanks You' SET @FilePath = 'D:\SQL Server Training\SSIS\Data\Export\TestData.xls' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyGmail', @recipients = 'mailrecipient@organisation.com', @subject = @Subject, @body = @MailBody, @importance = 'HIGH', @file_attachments = @FilePath;
Example 3: Send Plain Text Email with SQL Query Result and Attachment
DECLARE @MailBody NVARCHAR(MAX), @Subject NVARCHAR(200), @FIlePath NVARCHAR(MAX), @SQLStmt NVARCHAR(MAX) SET @Subject = N'Database Mail Status: ' + CONVERT(VARCHAR(20), GETDATE(), 100) SET @MailBody = N'Hello, This is an autogenerated mail from SQL Database Server 01. In case of any concerns please contact sqldb01.mailadmin@organisation.com . Thanks You' SET @FilePath = 'D:\SQL Server Training\SSIS\Data\Export\TestData.xls' SET @SQLStmt = N'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyGmail', @recipients = 'mailrecipient@organisation.com', @subject = @Subject, @body = @MailBody, @query = @SQLStmt, @attach_query_result_as_file = 1, @importance = 'HIGH', @file_attachments = @FilePath;
Hope this post helps you with a new learning.
SQL Journey 