mssql send mail msdb.dbo.sp_send_dbmail

2015-05-26

348
0
分享
2015-05-28

摘要:mssql send mail msdb.dbo.sp_send_dbmail

需求：想多一個監控程式有沒有正常執行的管道（所以 AP已經有了，AP之外…那就是 DB了啊，相當直覺XD）；有異常就寄 EMAIL通知。

EMAIL很單純，麻煩的是 EMAIL內容…組 HTML TABLE好了，也很直覺。

試了幾種

CURSOR組 HTML
直接用 SQL組 HTML
最後發現直接在 sp_send_dbmail的 query直接下 sql就好了；雖然組出來的內容沒那麼好看（欄位名一長就歪歪的），但以這需求來說，不寄信才是正常的。

其實這篇想記的是方法 2組 HTML方式，常常覺得要用 CURSOR才做的到，然後接著又想到 CURSOR耗用更多資源…直接看程式吧。

--方法一
DECLARE @YourCursor CURSOR, 
	@COL1 nvarchar (50),
	@COL2 varchar (7),
	@html nvarchar(max)

SET @html='(table)(tr)'
SET @html=@html+'(td)COL1(/td)'
SET @html=@html+'(td)COL2(/td)'
SET @html=@html+'(/tr)'

SET @YourCursor = CURSOR FAST_FORWARD
FOR (
	SELECT COL1, COL2
	FROM YOURTABLE
)
OPEN @YourCursor

FETCH NEXT FROM @YourCursor
INTO @COL1, @COL2
WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @html=@html+'(tr)'
	SET @html=@html+'(td)'+ isnull(@COL1, '') +'(/td)'
	SET @html=@html+'(td)'+ isnull(@COL2, '') +'(/td)'
	SET @html=@html+'(/tr)'

	FETCH NEXT FROM @YourCursor
	INTO @COL1, @COL2
END
CLOSE @YourCursor
DEALLOCATE @YourCursor

SET @html=@html+'(/table)'

--方法二
DECLARE @html nvarchar(max)
SET @html=''
SET @html=@html+'(table border="1" cellpadding="3" cellspacing="2")(tr)'
SET @html=@html+'(td)COL1(/td)'
SET @html=@html+'(td)COL2(/td)'
SET @html=@html+'(/tr)'

SELECT @html=@html+'(tr)'
	, @html=@html+'(td)'+ isnull(COL1, '') +'(/td)'
	, @html=@html+'(td)'+ isnull(COL2, '') +'(/td)'
	, @html=@html+'(/tr)'
FROM YOURTABEL

SET @html=@html+'(/table)'

--方法三
declare @sql nvarchar(max)
set @sql = '
SELECT COL1, COL2
FROM YOURTABLE
'
exec msdb.dbo.sp_send_dbmail
	@profile_name = @profile_name, --設定檔
	@recipients = @receiver, --收件者
	@subject = @subject, --主旨
	@query = @sql,
	@execute_query_database = @execute_query_database, --database context
	@attach_query_result_as_file = 0, --bit, 0:郵件內顯示、1:附檔
	@query_result_header = 1, --bit, 1:顯示表頭
	--@query_result_width =  --int, with a default of 256. The value provided must be between 10 and 32767
	--@query_result_separator =  --char(1). Defaults to ' ' (space)
	--@query_no_truncate = 1,--bit. 欄位包含大檔 ex: nvarchar(max)使用，可能會影響效能
	@exclude_query_output = 1, --bit, default 0, 1:sp_send_dbmail stored procedure does not print any of the query execution messages on the console
	@append_query_error = 0 --bit, with a default of 0. 錯誤不寄信

當然，方法一跟二都有呼叫 msdb.dbo.sp_send_dbmail的段落；HTML角括號照例是要 REPLACE掉滴。

T-SQL

回首頁