SQL Server database email, SQL Server database

SQL Server database email, SQL Server database

The mail sending function provided by SQLserver allows you to easily send information in the database to relevant personnel. For example, you can send the warning information to the management personnel in conjunction with the monitoring event, and send a report to the relevant personnel in conjunction with the job.

The mail configuration in the database is very simple. The following figure shows the Configuration:

1. Manage-database mail (right-click)-configure database mail

2. Open the database mail Configuration Wizard and select the first email"Install the database email by executing the following characters:"

3. First, "Create a configuration file" and enter"Configuration file nameAnd then clickAddSMTP account

4. Create an email account

Account name:Email account name displayed in the database

Note:(Optional)

Email Address:The email address used to send emails to the database.

Display name:The name displayed on the email sent by this account. (Optional)

Reply Email:When the recipient replies to an email in the database, the reply will be sent to the account. (Optional)

Server Name:The name or IP address of the SMTP server used to send the email. Generally, 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 communication.

Windows authentication using Database Engine service creden:Use the creden configured for the SQL Server database engine service to generate a connection to the SMTP Server (for example, an SMTP Server is deployed in the LAN ).

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

User name:The username 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:Send the email to the SMTP server without logon creden (for example, an SMTP server is deployed in the LAN ).

5. Select"Public configuration file"

Public configuration filePairMsdbIn the databaseDatabaseMailUserRoleAll members of the database role are available. They allowDatabaseMailUserRoleAll members of the role use this configuration file to send an email.

Dedicated configuration fileIsMsdbThe security subject in the database. They only allow specified database users, roles, andSysadminMembers of the fixed server role can use this configuration file to send emails.

6."Configure System Parameters"

7. click confirm next step !~

8. Start testing now !~ Manage-data emails (right-click)-send test emails

9. Enter"Recipient", Click"Send test email"

10. The test email was received quickly. It was successful !~ O (distinct _ distinct) O ~

Now, configure the email using an SQL script:

-- 1. enable database mail exec sp_configure 'show advanced options', 1 reconfigure with overridegoexec sp_configure 'database mail xps ', 1 reconfigure with overridego -- 2. create an email account exec msdb. dbo. sysmail_add_account_sp @ account_name = N 'kk _ 8080', -- account name @ description = N 'user kk ', -- description @ email_address = n' ****** 6411@139.com ', -- mail address @ display_name = N 'ad08', -- display name @ replyto_address = n' ***** 0961@qq.com ', -- reply email @ mailserver_type = n' smt P', -- mail protocol @ mailserver_name = n' smtp .139.com ', -- mail server name @ port = 25, -- mail server port @ enable_ssl = 0, -- whether encrypted @ use_default_credentials = 0, -- Authentication mode @ username = n' ****** 6411@139.com ', -- username @ password = n' ************* ', -- password @ account_id = nullgo -- 3. create the configuration file exec msdb. dbo. sysmail_add_profile_sp @ profile_name = n' ultprofile profile ', -- name of the new configuration file @ description = n' default configuration file', -- description of the new configuration file (optional) @ profile_id = nullgo -- 4. the account is associated with the configuration file exec msdb. dbo. sys Mail_add_profileaccount_sp @ profile_name = N 'defaultprofile ', -- configuration file name @ account_name = n'kk _ 139', -- account name @ sequence_number = 1 -- sequential go -- 5. send the email exec msdb. dbo. sp_send_dbmail @ profile_name = N 'defaultprofile ', -- profile name @ recipients = n' ***** 5479@qq.com', -- recipient @ subject = n' database mail test ', -- test email on mail subject @ body = n' AD08. '-- Mail content go -- delete the configuration file EXEC msdb. dbo. sysmail_delete_profile_sp @ profile_name = N 'defaultprofile ', @ force_delete = True -- delete the account EXEC msdb. dbo. sysmail_delete_account_sp @ account_name = n' kk _ 100' -- 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

Refer:

Database email

SQL Server creates database emails

Configure SQL Server 2012 database email