編寫人：CC阿爸
2014-6-14
多話不講，請參考以下腳本
use master
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Database mail XPs',1
go
reconfigure
go
DECLARE @PRofileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
SET @ProfileName = 'SystemMail';
SET @AccountName = 'SystemMail';
--Initial Cleanup:
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
p.name = @ProfileName AND a.name = @AccountName)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE sysmail_delete_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName
END
IF EXISTS( SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE sysmail_delete_profile_sp
@profile_name = @ProfileName
END
IF EXISTS( SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
BEGIN
PRINT 'Deleting Account.'
EXECUTE sysmail_delete_account_sp
@account_name = @AccountName
END
exec msdb..sysmail_add_account_sp
@account_name = 'SystemMail' -- 郵件帳戶名稱（SQL Server 使用）
,@email_address = 'potrontech@163.com' -- 發件人郵件地址
,@display_name = 'SystemMail' -- 發件人姓名
,@replyto_address = 'potrontech@163.com'
,@description = 'system alert'
,@mailserver_name = 'smtp.163.com' -- 郵件服務器地址
,@mailserver_type = 'SMTP' -- 郵件協議（SQL 2005 只支持SMTP）
,@port = 25 -- 郵件服務器端口
,@username = 'potrontech@163.com' -- 用戶名
,@passWord = 'xxxxx' -- 此處為郵件密碼。暫以x代替
,@use_default_credentials = 0
,@enable_ssl = 0
,@account_id = null
exec msdb..sysmail_add_profile_sp @profile_name = 'SystemMail' -- profile 名稱
,@description = 'system alert' -- profile 描述
,@profile_id = null
exec msdb..sysmail_add_profileaccount_sp@profile_name = 'SystemMail' -- profile 名稱
,@account_name = 'SystemMail' -- account 名稱
,@sequence_number = 1 -- account 在profile 中順序
------test databasemail
----DECLARE @xml NVARCHAR(MAX)
----DECLARE @body NVARCHAR(MAX)
------SET @xml =CAST(( SELECT ID AS 'td','',UserID AS 'td' ,'',UserName AS 'td' from ERV.dbo.CA FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
----SET @body ='<html><H1>Sales Reports</H1><body ><table border = 1><tr><td>ID</td><td>UserID</td><td>UserName</td></tr>'
----SET @body = @body + @xml +'</table></body></html>'
----
----
------test databasemail
----
----EXEC msdb.dbo.sp_send_dbmail
----@recipients=N'potrontech@163.com',
----@body= @body,
----@body_format ='HTML',
----@subject = 'Test Database Mail html',
----@profile_name = 'SystemMail'
----go
----use msdb
----
------要檢查消息是否發送成功，我可以對sysmail_allitems系統視圖執行一次查詢。
--select * from sysmail_allitems
--select * from sysmail_mailitems
--select * from sysmail_event_log
--將一個配置文件設置為數據庫的默認公共配置文件
exec msdb.dbo.sysmail_help_principalprofile_sp
EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
@principal_name = 'public',
@profile_name = 'SystemMail',
@is_default = '1';
有關更多的技術分享，大家可以加入我們的技術群。
歡迎加入技術分享群：238916811
新聞熱點
疑難解答
