Using SQL Server stored procedure SP

This article graphically describes the use of SQL Server stored procedures sp_send_dbmail Send mail method, we refer to the use of the

1 Create a profile and account (create a profile and configure Database Mail Wizard to access the accounts used in the Database Mail node and its context menu in the Metabase Mail Management node). )

The profile name here is used as a parameter when using sp_send_dbmail

Point "Add"

Where the account name can be arbitrarily specified (describe the function can), the focus is the mail sending server (SMTP) configuration: The e-mail address is the sender's mail address, the display name is the sender name, reply e-mail (can be the same as the e-mail address), the server name is the server send server (eg: QQ for smtp.exmail.qq.com) The port number is unchanged; SMTP Authentication: Basic Authentication (Configure your own message sender address and password).

You can add multiple sender accounts and one fails to send with another.

Point "Next"

2) Configure email

--You can manually use SQL code to start the Database Mail feature (if you do not use SQL code to start the Database Mail feature, during the Metabase Mail Wizard, you will be prompted "whether to start the Database Mail feature", select "Yes")

exec sp_configure ' show advanced options ', 1

Reconfigure

Go

exec sp_configure ' DATABASE MAIL XPS ', 1

Reconfigure

Go

/*

Database Mail will keep a copy of the email sent and we can query through Sysmail_allitems, Sysmail_sentitems, Sysmail_unsentitems, Sysmail_faileditems. The sending status of the message will be saved in the Sysmail_mailitems table, the Sent_status column in the table will be displayed as 1 when it is successfully sent, and the Sent_status column will appear as 2 when the send fails, 3 when it is sent.

As shown below, the log file can be viewed in the Sysmail_log table

*/

3) Send email.

--or execute stored procedures under the msdb database

EXEC sp_send_dbmail @profile_name = ' db_mail_test ',

@recipients = ' test@Example.com ',

@subject = ' Test message ',

@body = ' Test sending Database Mail, success. '

Point "Send ..."

Finally to the mailbox or to view the mail log verification.