SQL Server Database Mail

SQL Server comes with a mail-sending feature that makes it easy to send information from a database to relevant people. If the warning information can be sent to the management personnel in conjunction with the monitoring event, the report can be dispatched to the relevant personnel in conjunction with the job.

Database Mail configuration is simple, the following text configuration:

1. Manage-Database Mail (right-click)-Configure Database Mail

2. Open the Database Mail Configuration Wizard and select the first "install Database Mail by performing the following people "

3. First "New profile", fill in "configuration file name ", then click Add SMTP account

4. Create a mail account

account name: mail account names seen in the database

Description:(optional)

e-mail address: The e-mail address in the database where e-mail is sent.

Display Name: The name that is displayed on the e-mail message sent by this account. (option available)

reply e-mail: when the recipient replies to a message in the database, the reply is sent to the account. (option available)

Server name: the name or IP address of the SMTP server used to send the e-mail. Typically this format is similar to smtp.<your_company>.com.

Port Number: The port number of the SMTP server for this account. Most SMTP servers use port 25.

This server requires secure connection (SSL): use Secure Sockets Layer to encrypt traffic.

Windows authentication using Database engine service credentials: generates a connection to the SMTP server using the credentials configured for the SQL Server Database engine service (such as an SMTP server deployed on a local area network).

Basic Authentication: Specifies the user name and password required by the SMTP server (using an external SMTP server).

User name: the user name used to log on to the SMTP server.

Password: The password used to log on to the SMTP server.

Confirm Password: Enter the password again for confirmation.

Anonymous Authentication: sends a message to an SMTP server (such as an SMTP server deployed on a local area network) without login credentials.

5. Select " Public Profile"

The public configuration file is available to all members of the DatabaseMailUserRole database role in the msdb database. They allow all members of the DatabaseMailUserRole role to use the profile to send e-mail messages.

The private configuration file is defined for the security principal in the msdb database. They allow only the specified database users, roles, and members of the sysadmin fixed server role to use the profile to send e-mail messages.

6. " Configure system Parameters "

7. Next step, click OK to finish! ~

8. Start testing Now! ~ Manage--mail in data (right button)--Send test email

9. Fill in the "to" email and click " send Test Email"

10. Test mail soon received, success! ~o (∩_∩) o~

Now configure the message in SQL script:

--1. Enable Database Mail exec sp_configure ' show advanced options ', 1reconfigure with overridegoexec sp_configure ' Database Mail xps ', 1reconfigure with overridego--2. Create mail account exec msdb.dbo.sysmail_add_account_sp@account_name = N ' kk_139 ',-- Account name @description = n ' user kk ',--description @email_address = n ' *******[email protected] ',--mail address @display_name = N ' AD08 ',-- Display name @replyto_address = N ' *****[email protected] ',--reply e-mail @mailserver_type = n ' smtp ',--mail protocol @mailserver_name = N ' smtp.139.com ',--mail server name @port =25,--mail server port @enable_ssl =0,--encryption @use_default_credentials =0,--authentication Mode @username = N ' [email protected] ',--username @password = N ' ************ ',--password @account_id = nullgo--3. Create profile exec Msdb.dbo.sysmail_add_profile_sp@profile_name = N ' defaultprofile ',--the name of the new configuration file @description = N ' default profile ',-- Description of the new configuration file (optional) @profile_id = nullgo--4. Account associated with profile exec msdb.dbo.sysmail_add_profileaccount_sp @profile_name = N ' defaultprofile ',--profile name @account_name = N ' kk_139 ',--account name @sequence_number = Order go--5. Send mail exec msdb.dbo.sp_send_dbmail@profile_name = N ' defaultprofile ',--profile name @recipients = N ' *****[email protected] ',--recipient @subject = N ' Database Mail test ',--message header @body = N ' AD08 on the test e-mail. '--Message content go--Delete profile exec msdb.dbo.sysmail_delete_profile_sp @profile_name =n ' defaultprofile ', @force_delete = true--Delete account exec msdb.dbo.sysmail_delete_account_sp @account_name =n ' kk_139 '-related query exec Msdb.dbo.sysmail_help_profile _spexec msdb.dbo.sysmail_help_account_spexec msdb.dbo.sysmail_help_profileaccount_spexec Msdb.dbo.sysmail_help_ Configure_spselect * from Msdb.dbo.sysmail_accountselect * from Msdb.dbo.sysmail_profile select * from Msdb.dbo.sysmail_ Profileaccount SELECT * FROM Msdb.dbo.sysmail_configurationselect * from Msdb.dbo.sysmail_allitemsselect * from msdb.dbo . sysmail_event_log

Reference:

Database Mail

SQL Server creates Database Mail

Configuring SQL Server 2012 Database Mail

SQL Server Database Mail