- 環境:
- MSSQL 2008R2
- 參考資料:
- https://vito-sql.blogspot.tw/2013/10/database-mail.html
- https://msdn.microsoft.com/zh-tw/library/ms190307.aspx
- https://technet.microsoft.com/zh-tw/library/ms188719(v=sql.105).aspx
- https://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10553.aspx
- https://dba.stackexchange.com/questions/168395/execute-sql-statement-and-send-result-to-email
透過組態精靈設定:
啟用Database Mail 功能:
點選加入設定你要使用的SMTP帳戶。
新增DatabaseMail帳戶,請設定容易辨識的名稱,在發信時會用到,目前範例為DBMail。
測試是否設定正確:
使用預存程序發送郵件:
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @mailitem_id1 varchar(100) | |
DECLARE @ErrorID INT | |
DECLARE @RtnCode INT | |
EXEC @RtnCode =msdb.dbo.sp_send_dbmail | |
@recipients = 'admin@gmail.com', | |
@profile_name = 'DBMail', | |
@subject = 'TestMail From Database', | |
@body_format='HTML', | |
@body = '<B>TestMail</B><a>Content</a>', | |
@mailitem_id=@mailitem_id1 output | |
--錯誤內容 | |
SET @ErrorID = @@ERROR | |
IF @RtnCode <> 0 BEGIN | |
SELECT @ErrorID | |
END | |
ELSE | |
BEGIN | |
SELECT 'success' | |
END | |
但仔細看一下發信的內容使用到了msdb的資料庫權限,所以不是管理者權限的帳號,
必須授權msdb下的DatabaseUserRole。
沒有留言:
張貼留言