利用SQLServer資料庫發送郵件

彙總篇： http://www.cnblogs.com/dunitian/p/4822808.html#tsql

這個應用案例很多，一般都是預警，比如異常連接配接的時候，或者資料庫報錯的時候。等等，，，

先回顧一下以前用C#發郵件的内容：

http://www.cnblogs.com/dunitian/p/5682930.html

可能有些人看不太懂，這個是簡單示範：

https://github.com/dunitian/LoTCodeBase/blob/master/NetCode/0.知識拓展/01.網絡程式設計/3.System.Net.Mail/3.3.SmtpClient/EmailHelper.cs

步入正軌：

在配置之前請先把郵件的POP3之類的設定一下：

簡圖：

其他郵箱都是類似的，可以參考這個：

http://www.cnblogs.com/dunitian/p/5682930.html#god

圖形化示範：

配置名字随意取，可以用項目名。顯示名稱建議用版本号+伺服器ip，這樣出問題可以定位跟蹤

微軟圖形化的東西一般有個特點，一路下一步基本上能解決所有基礎問題

勾選一下（貌似不勾選也沒事）

測試一下：

發一封郵件到“我為NET狂”的官方郵件去

去看看：

指令示範：（不需要記，你又不是DBA，會用即可）

腳本： 

--開啟發郵件功能
exec sp_configure 'show advanced options',1
reconfigure with override  
go
exec sp_configure 'database mail xps',1 
reconfigure with override  
go

--建立郵件帳戶資訊 
exec msdb.dbo.sysmail_add_account_sp
  @account_name ='dunitian1',					-- 郵件帳戶名稱    
  @email_address ='[email protected]',				-- 發件人郵件位址     
  @display_name ='SQLServer2014_192.168.1.20',	-- 發件人姓名  
  @MAILSERVER_NAME = 'smtp.yeah.net',			-- 郵件伺服器位址 
  @PORT =25,									-- 郵件伺服器端口  
  @USERNAME = '[email protected]',					-- 使用者名  
  @PASSWORD = '郵件密碼'							-- 密碼   
GO
--資料庫配置檔案
exec msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'SQLServer_DotNetCrazy1',		-- 配置名稱  
  @description = '資料庫郵件配置檔案'			-- 配置描述
go

--使用者和郵件配置檔案相關聯
exec msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'SQLServer_DotNetCrazy1',		-- 配置名稱
  @account_name = 'dunitian1',					-- 郵件帳戶名稱     
  @sequence_number = 1							-- account 在 profile 中順序（預設是1）
go      

發送郵件腳本：

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServer_DotNetCrazy1',	--配置名稱
@recipients = '[email protected]',	--收件名稱
@body_format = 'HTML',						--内容格式
@subject = '文章标題',
@body = '郵件内容'      

結果：20的ip也發過來了

--相關查詢

--select * from msdb.dbo.sysmail_allitems

--select * from msdb.dbo.sysmail_faileditems --失敗狀态的消息

--select * from msdb.dbo.sysmail_unsentitems --看未發送的消息

--select * from msdb.dbo.sysmail_sentitems --檢視已發送的消息

--select * from msdb.dbo.sysmail_event_log --記錄日記

參考文章：

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

exec sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE  
go
exec sp_configure 'database mail xps',1 
RECONFIGURE WITH OVERRIDE  
go

--2.建立郵件帳戶資訊 
EXEC msdb..Sysmail_add_account_sp
  @ACCOUNT_NAME ='OCTMamiETL',-- 郵件帳戶名稱    
  @EMAIL_ADDRESS ='[email protected]',-- 發件人郵件位址     
  @DISPLAY_NAME ='系統管理者',-- 發件人姓名  
  @REPLYTO_ADDRESS =NULL,
  @DESCRIPTION = NULL,
  @MAILSERVER_NAME = 'SMTP.163.COM',-- 郵件伺服器位址    
  @MAILSERVER_TYPE = 'SMTP',-- 郵件協定      
  @PORT =25,-- 郵件伺服器端口  
  @USERNAME = '[email protected]',-- 使用者名  
  @PASSWORD = 'ABC123',-- 密碼   
  @USE_DEFAULT_CREDENTIALS =0,
  @ENABLE_SSL =0,
  @ACCOUNT_ID = NULL

GO

--3.資料庫配置檔案
IF EXISTS(SELECT name
          FROM   msdb..sysmail_profile
          WHERE  name = N'ETLErrorProfileLog')
  BEGIN
      EXEC msdb..Sysmail_delete_profile_sp @profile_name='ETLErrorProfileLog'
  END

EXEC msdb..Sysmail_add_profile_sp
  @profile_name = 'ETLErrorProfileLog',-- profile 名稱  
  @description = '資料庫郵件配置檔案',-- profile 描述    
  @profile_id = NULL

go

--4.使用者和郵件配置檔案相關聯
EXEC msdb..Sysmail_add_profileaccount_sp
  @profile_name = 'ETLErrorProfileLog',-- profile 名稱   
  @account_name = 'OCTMamiETL',-- account 名稱     
  @sequence_number = 1 -- account 在 profile 中順序
--5.發送文本測試郵件
EXEC msdb..Sp_send_dbmail
  @profile_name='ETLErrorProfileLog',
  @recipients='[email protected]',--收件人
  @subject='Test title this is test ',
  @body=N'z中文郵件内容  中文郵件内容'

go

-----------------------------------------------------------------------------------
/*
    功能說明：啟用警報系統裡面的郵件配置
    參數說明: xp_instance_regwrite 修改系統資料庫
*/
EXEC msdb.dbo.Sp_set_sqlagent_properties @email_save_in_sent_folder=1

EXEC master.dbo.Sp_mssetalertinfo @pagersendsubjectonly = 0 --啟用警報系統 【在通知消息中包含電子郵件的正文】

EXEC master.dbo.Xp_instance_regread
  N'HKEY_LOCAL_MACHINE',--啟用警報系統 【啟用郵件配置檔案】
  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
  N'DatabaseMailProfile' ---郵件系統【選擇資料庫郵件】

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',--啟用警報系統 【啟用郵件配置檔案】
  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
  N'DatabaseMailProfile',---郵件系統【選擇資料庫郵件】
  N'REG_SZ',
  'ETLErrorProfileLog' ---郵件配置檔案【郵件配置裡面的SQLMailProfile】   

-----------------------------------------------------------------------------------
DECLARE @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 -- 操作員
  END

EXEC msdb.dbo.Sp_add_operator
  @name = @Sys_OperatorsName,
  @enabled = 1,
  @weekday_pager_start_time = 90000,
  @weekday_pager_end_time = 180000,
  @saturday_pager_start_time = 90000,
  @saturday_pager_end_time = 180000,
  @sunday_pager_start_time = 90000,
  @sunday_pager_end_time = 180000,
  @pager_days = 127,
  @email_address = '[email protected]',-----僅可以對 SQL Mail 使用電子郵件别名。必須對資料庫郵件使用電子郵件位址。
  @pager_address = N'',
  @netsend_address = N''

/*
    功能說明： 在Job中添加操作員的操作
    參數說明:  @notify_level_email指定何時将該作業的項放入 Microsoft Windows 應用程式日志。
               eventlog_level 的資料類型為 int 【0 從不 1 成功時 2 失敗時 3 始終】         
    修改說明：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',---對應的作業名稱
        @notify_level_email = 2,
        @notify_level_netsend = 2,
        @notify_level_page = 2,
        @notify_email_operator_name = 'BSMicheal' ---對應的操作員
  END;      

作者：

毒逆天

出處：

https://www.cnblogs.com/dotnetcrazy

打賞：

18i4JpL6g54yAPAefdtgqwRrZ43YJwAV5z

本文版權歸作者和部落格園共有。歡迎轉載，但必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接！