sending mail using SQL Server database

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

This application is a lot of cases, is generally an early warning, such as abnormal connection time, or database error. Wait a minute

Let's look back at what you used to post in C #: http://www.cnblogs.com/dunitian/p/5682930.html

Some people may not understand, this is a simple demonstration: https://github.com/dunitian/LoTCodeBase/blob/master/NetCode/0. Knowledge development/01. Network Programming/3.system.net.mail /3.3.smtpclient/emailhelper.cs

Getting on track:

Before configuring, please set up the POP3 and the like of the Mail first:

Diagrams:

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

Graphical presentation:

Configure the name to take, you can use the project name. Display Name recommended version number + server IP, so problems can be tracked

Microsoft graphical things generally have a feature, the next step will basically solve all the basic problems

Check (it looks like you're not checking it.)

Test it:

Send an e-mail to "I'm a net crazy" official mail to

To see:

Command Demo: (No need to remember, you're not a DBA, you can use it)

Script:

--Open outgoing mail function exec sp_configure ' show advanced options ', 1reconfigure 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 ',- -Mail account name      @email_address = ' [email protected] ',--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 = ' [email protected] ',--username c8/> @PASSWORD = ' mail password '--password   go--database configuration file exec msdb.dbo.sysmail_add_profile_sp  @profile_name = ' Sqlserver_ DotNetCrazy1 ',--configuration name    @description = ' Database Mail profile '--Configuration description go--user and mail profile associated exec msdb.dbo.sysmail_add_ profileaccount_sp  @profile_name = ' sqlserver_dotnetcrazy1 ',--configuration name  @account_name = ' Dunitian1 ',--mail account name       @sequence_number = The order of the account in the profile (default is 1) go

Send mail script:

exec msdb.dbo.sp_send_dbmail@profile_name = ' sqlserver_dotnetcrazy1 ',--configuration name @recipients = ' [email protected] ',-- Pickup name @body_format = ' HTML ',--content format @subject = ' article title ', @body = ' message content '

Result: 20 of the IP was sent over.

--related inquiries
--select * from Msdb.dbo.sysmail_allitems
--select * FROM Msdb.dbo.sysmail_faileditems--message of failed status
--select * FROM Msdb.dbo.sysmail_unsentitems--See unsent messages
--select * FROM Msdb.dbo.sysmail_sentitems--View sent messages

--select * from msdb.dbo.sysmail_event_log--record diary

Reference article:

Http://www.cnblogs.com/junqingday/p/4187161.html

exec sp_configure ' show advanced options ', 1RECONFIGURE with OVERRIDE goexec sp_configure ' Database Mail xps ', 1 Reconfigur E with OVERRIDE go--2. Create mail account information EXEC msdb.       sysmail_add_account_sp @ACCOUNT_NAME = ' Octmamietl ',--mail account name @EMAIL_ADDRESS = ' [email protected] ',--sender e-mail address @DISPLAY_NAME = ' system Administrator ',--sender name @REPLYTO_ADDRESS =null, @DESCRIPTION = NULL, @MAILSERVER_NAME = ' smtp.163.com ',--    Mail server address @MAILSERVER_TYPE = ' SMTP ',--Mail protocol @PORT =25,--mail server port @USERNAME = ' [email protected] ',--user name @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 Mail configuration file ',--profile description @profile_id = nullgo--4. User and MailPiece profile associated with exec msdb. sysmail_add_profileaccount_sp @profile_name = ' etlerrorprofilelog ',--profile name @account_name = ' Octmamietl ',--acco UNT name @sequence_number = 1-The account is sequentially--5 in the profile. Send text test message exec msdb: Sp_send_dbmail @profile_name = ' Etlerrorprofilelog ', @recipients = ' [email protected] ',--recipient @subject = ' Test title ' This is test ', @body =n ' z Chinese e-mail content ' Go-----------------------------------------------------------------------------------/* Feature Description: Enable message configuration parameter description in Alert system: Xp_instance_regwrite Modifying the registry */exec msdb.dbo.Sp_set_sqlagent_properties @email_save_in_sent_folder =1exec  Master.dbo.Sp_mssetalertinfo @pagersendsubjectonly = 0--Enable the alert system "include the body of the email in the notification message" EXEC Master.dbo.Xp_instance_regread N ' HKEY_LOCAL_MACHINE ',--Enable alert system "mail-enabled profile" n ' software\microsoft\mssqlserver\sqlserveragent ', n ' databasemailprofile '---mail system ' Select Database Mail ' EXEC master.dbo.Xp_instance_regwrite n ' HKEY_LOCAL_MACHINE ', n ' software\microsoft\mssqlserver\ SQLServerAgent ', n ' usedatabasemail ', n ' REG_DWORD ',EXEC master.dbo.Xp_instance_regwrite n ' HKEY_LOCAL_MACHINE ',--Enable alert system "mail-enabled profile" N ' software\microsoft\mssqlserver\ SQLServerAgent ', n ' databasemailprofile ',---mail system "Select Database Mail" n ' REG_SZ ', ' Etlerrorprofilelog '---mail profile " Mail configuration inside the Sqlmailprofile "----------------------------------------------------------------------------------- DECLARE @Sys_OperatorsName VARCHAR 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, @enab LED = 1, @weekday_pager_start_time = 90000, @weekday_pager_end_time = 180000, @saturday_pager_start_time = 90000, @sat Urday_pager_end_time = 180000, @sunday_pager_start_time = 90000, @sunday_pager_end_time = 180000, @pager_days = 127, @ email_address = ' [email protected] ',-----can only use e-mail aliases for SQL Mail.  You must use an e-mail address for Database Mail. @pager_address = N ", @netsend_address = N '/* Function Description: Add operator's action parameter description in job: @notify_level_email Specify when the item for the job is placed in the Microsoft Windows application log. Eventlog_level data type is int "0 never 1 succeeds when 2 fails 3 always" modification Description: Create by-LY on 2011-010-10*/if EXISTS (SEL ECT 1 from msdb.dbo.sysjobs WHERE name = ' Dad ') BEGIN EXEC msdb.dbo.Sp_update_job @job        _name = ' Dad ',---corresponding job name @notify_level_email = 2, @notify_level_netsend = 2, @notify_level_page = 2, @notify_email_operator_name = ' bsmicheal '---the corresponding operator END;

sending mail using SQL Server database