You use the sp_send_dbmail
Usage
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name', @recipients = 'recipient1@example.com[;recipient2; ...recipientn]', @subject = 'subject', @body = 'message_body', [@body_format = 'HTML'], [@file_attachments = 'file_path1;file_path2; ...file_pathn'], [@query = 'SQL_query'], [@attach_query_result_as_file =0|1]';
The following parameters are required:
-
@profile_name– The name of the Database Mail profile from which to send the message. -
@recipients– The semicolon-delimited list of email addresses to which to send the message. -
@subject– The subject of the message. -
@body– The body of the message. You can also use a declared variable as the body.
The following parameters are optional:
-
@body_format– This parameter is used with a declared variable to send email in HTML format. -
@file_attachments– The semicolon-delimited list of message attachments. File paths must be absolute paths. -
@query– A SQL query to run. The query results can be attached as a file or included in the body of the message. -
@attach_query_result_as_file– Whether to attach the query result as a file. Set to 0 for no, 1 for yes. The default is 0.
Examples
The following examples demonstrate how to send email messages.
Example of sending a message to a single recipient
USE msdb
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'nobody@example.com',
@subject = 'Automated DBMail message - 1',
@body = 'Database Mail configuration was successful.';
GOExample of sending a message to multiple recipients
USE msdb
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'recipient1@example.com;recipient2@example.com',
@subject = 'Automated DBMail message - 2',
@body = 'This is a message.';
GOExample of sending a SQL query result as a file attachment
USE msdb
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'nobody@example.com',
@subject = 'Test SQL query',
@body = 'This is a SQL query test.',
@query = 'SELECT * FROM abc.dbo.test',
@attach_query_result_as_file = 1;
GOExample of sending a message in HTML format
USE msdb
GO
DECLARE @HTML_Body as NVARCHAR(500) = 'Hi, <h4> Heading </h4> </br> See the report. <b> Regards </b>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'nobody@example.com',
@subject = 'Test HTML message',
@body = @HTML_Body,
@body_format = 'HTML';
GOExample of sending a message using a trigger when a specific event occurs in the database
USE AdventureWorks2017
GO
IF OBJECT_ID ('Production.iProductNotification', 'TR') IS NOT NULL
DROP TRIGGER Purchasing.iProductNotification
GO
CREATE TRIGGER iProductNotification ON Production.Product
FOR INSERT
AS
DECLARE @ProductInformation nvarchar(255);
SELECT
@ProductInformation = 'A new product, ' + Name + ', is now available for $' + CAST(StandardCost AS nvarchar(20)) + '!'
FROM INSERTED i;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'nobody@example.com',
@subject = 'New product information',
@body = @ProductInformation;
GO