sending mail using SQL Server database

Source: Internet
Author: User
Tags mssqlserver mail account server port

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.