Use SQLServer database to send emails, and sqlserver to send emails

Use SQLServer database to send emails, and sqlserver to send emails

Summary: http://www.cnblogs.com/dunitian/p/4822808.html#tsql

There are many application cases, usually warning, such as abnormal connection or database error. Wait ,,,

Let's look back at the previous C # E-mail: http://www.cnblogs.com/dunitian/p/5682930.html

Some may not understand it. This is a simple demonstration: Explain

 

On track:

Before configuring, set mail POP3 and the like:

Diagram:

 

Other mailboxes are similar, you can refer to this: http://www.cnblogs.com/dunitian/p/5682930.html#god

Graphical Demonstration:

 

You can use the project name to retrieve the configuration name. We recommend that you use the version number + Server ip address to display the name. In this way, you can locate and track problems.

 

 

Microsoft's graphical things generally have a feature, and the next step can basically solve all basic problems.

Check it (it seems that it is okay if you do not check it)

Test:

Send an email to the official email "I am crazy about NET ".

Go and see:

Command Demonstration: (you don't need to remember, you are not a DBA, you can use it)

Script:
-- Enable the mail sending function exec sp_configure 'show advanced options', 1 reconfigure with override goexec sp_configure 'database mail xps ', 1 reconfigure with override go -- Create mail account information exec msdb. dbo. sysmail_add_account_sp @ account_name = 'dunitian1', -- email account name @ email_address = 'xxxx @ yeah.net ', -- Sender email address @ display_name = 'sqlserver2014 _ 192.168.1.20 ', -- sender name @ MAILSERVER_NAME = 'smtp .yeah.net ', -- mail server address @ PORT = 25, -- mail server PORT @ USERNAME = 'xxxx @ yeah.net ', -- username @ PASSWORD = 'email password' -- password go -- Database Configuration File exec msdb. dbo. sysmail_add_profile_sp @ profile_name = 'sqlserver _ DotNetCrazy1 ', -- configuration name @ description = 'database mail configuration file' -- configuration description go -- the user and mail configuration file are associated with exec msdb. dbo. sysmail_add_profileaccount_sp @ profile_name = 'sqlserver _ DotNetCrazy1 ', -- configuration name @ account_name = 'dunitian1', -- mail account name @ sequence_number = 1 -- order of accounts in profile (1 by default) go
 
 
Email sending script:
Exec msdb. dbo. sp_send_dbmail @ profile_name = 'sqlserver _ DotNetCrazy1 ', -- configuration name @ recipients = 'dotnetcrazy @ foxmail.com', -- receiving name @ body_format = 'html ', -- content format @ subject = 'Article title', @ body = 'email Content'
Result: the ip address of 20 is also sent.
 
-- Query
-- Select * from msdb. dbo. sysmail_allitems
-- Select * from msdb. dbo. sysmail_faileditems -- message in the failed status
-- Select * from msdb. dbo. sysmail_unsentitems -- View unsent messages
-- Select * from msdb. dbo. sysmail_sentitems -- View sent messages
-- Select * from msdb. dbo. sysmail_event_log -- Record the log

 

 

References:

Http://www.cnblogs.com/junqingday/p/4187161.html
Exec sp_configure 'show advanced options', 1 reconfigure with override goexec sp_configure 'database mail xps ', 1 reconfigure with override go -- 2. create email account information EXEC msdb .. sysmail_add_account_sp @ ACCOUNT_NAME = 'ocmamietl ', -- email account name @ EMAIL_ADDRESS = 'ocmamietl @ 163.com', -- Sender email address @ DISPLAY_NAME = 'System Postmaster ', -- sender name @ REPLYTO_ADDRESS = NULL, @ DESCRIPTION = NULL, @ MAILSERVER_NAME = 'smtp. 163. COM ', -- mail server address @ MAILSE RVER_TYPE = 'smtp ', -- mail protocol @ PORT = 25, -- mail server PORT @ USERNAME = 'ocmamietl @ 163.com', -- USERNAME @ PASSWORD = 'abc123 ', -- password @ USE_DEFAULT_CREDENTIALS = 0, @ ENABLE_SSL = 0, @ ACCOUNT_ID = NULLGO--3. database Configuration File if exists (SELECT name FROM msdb .. sysmail_profile WHERE name = n' ETLErrorProfileLog ') begin exec msdb .. sysmail_delete_profile_sp @ profile_name = 'etlerrorprofilelog 'ENDEXEC msdb .. sysmail_add_profile_sp @ profile _ Name = 'etlerrorprofilelog ', -- profile name @ description = 'database email configuration file', -- profile description @ profile_id = NULLgo -- 4. the user and the mail configuration file are associated with EXEC msdb .. sysmail_add_profileaccount_sp @ profile_name = 'etlerrorprofilelog ', -- profile name @ account_name = 'ocmamietl', -- account name @ sequence_number = 1 -- order of accounts in profile -- 5. send text test email EXEC msdb .. sp_send_dbmail @ profile_name = 'etlerrorprofilelog ', @ recipients = 'ocmamietl @ 163. Com ', -- recipient @ subject = 'test title this is test', @ body = n' z Chinese mail content' go forward/* function description: enable email configuration parameters in the alarm system: xp_instance_regwrite modify the Registry */EXEC msdb. dbo. sp_set_sqlagent_properties @ email_save_in_sent_folder = 1 EXEC master. dbo. sp_mssetalertinfo @ pagersendsubjectonly = 0 -- enable alarm system [Body of email in notification message] EXEC master. dbo. xp_instance_regr Ead N 'HKEY _ LOCAL_MACHINE ', -- enable the alarm system [enable mail configuration file] n' SOFTWARE \ Microsoft \ MSSQLServer \ SQLServerAgent ', N 'databasemailprofile '--- Select database mail in the mail system EXEC master. dbo. xp_instance_regwrite N 'HKEY _ LOCAL_MACHINE ', n' SOFTWARE \ Microsoft \ MSSQLServer \ SQLServerAgent', n' usedatabase-mail ', n' REG _ dword', EXEC master. dbo. xp_instance_regwrite N 'HKEY _ LOCAL_MACHINE ', -- enable the alarm system [enable mail configuration file] n' SOFTWARE \ Microsoft \ MSSQLServer \ SQLServerA Gent ', n' DatabaseMailProfile', --- Mail System [select database mail] n' REG _ SZ ', 'etlerrorprofilelog '--- SQLMailProfile in mail configuration file includeclare @ Sys_OperatorsName VARCHAR (100) SET @ Sys_OperatorsName = 'bsmicheal' if exists (SELECT name FROM msdb. dbo. sysoperators WHERE name = @ Sys_OperatorsName) begin exec msdb .. sp_delete_operator @ name = @ Sys _ OperatorsName -- operator ENDEXEC msdb. dbo. sp_add_operator @ name = @ Sys_OperatorsName, @ enabled = 1, @ users = 90000, @ users = 180000, @ saturday_pager_start_time = 90000, @ users = 180000, @ sunday_pager_start_time = 90000, @ sunday_pager_end_time = 180000, @ pager_days = 127, @ email_address = 'ocmamietl @ 163.com ', ----- only the email alias can be used for SQL Mail. You must use an email address for database emails. @ Pager_address = n'', @ netsend_address = n''/* Function Description: Add operator operation parameters in Job description: @ policy_level_email: specifies when to add the job item to the Microsoft Windows Application Log. The data type of eventlog_level is int [0 is never 1 when 2 fails 3 always] Description: Create by LY on 2011-010-10 */if exists (SELECT 1 FROM msdb. dbo. sysjobs WHERE name = 'Dad ') begin exec msdb. dbo. sp_update_job @ job_name = 'Dad ', --- job name @ policy_level_email = 2, @ policy_level_netsend = 2, @ policy_level_page = 2, @ resolve = 'bsmicheal' --- END of the operator;