奇怪ㄟ你!: Database Mail的範例

此範例式搭配我的EMS, 建立在SQL Server agent上的作業定期執行

重點:

1.只能發UTF-8格式

2.千萬要處理好Null欄位的問題啦!

USE [GJ_Test]

DECLARE @msg varchar(8000)

DECLARE @msg_subject varchar(200)

DECLARE @mailaddr varchar(500)

DECLARE @MaxRownum int

DECLARE @Iter int

--發通知信

SET @mailaddr = 'a@goldjoint.com.tw;w.you@goldjoint.com.tw;fa@goldjoint.com.tw;ni.l@goldjoint.com.tw'

--SET @mailaddr = 'ni.l@goldjoint.com.tw'

SET @msg_subject = '請安排信中所列儀器的相關校驗事宜'

if object_id('#EMails') is not null

begin

drop table #EMails

end

SELECT

RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Notice_Email = 1 and a.F_Notice_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails)

IF @MaxRownum > 0

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails)

SET @msg = '清單所列儀器請先安排相關校驗事宜' + char(13) + char(10)+ char(13) + char(10)

set @msg=''+@msg+' '

WHILE @Iter <= @MaxRownum

BEGIN

SET @msg = @msg + (SELECT '開始準備日期:'+F_Prepare_Date+' 應校驗日期:'+F_Cali_Date+' 編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+(CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">' as mg

FROM #EMails

WHERE RowNum = @Iter) + char(13) + char(10)+ ' '

SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + ' '+ ' 共' + cast(@MaxRownum as varchar) + '項'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr,

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;

UPDATE T_EMS_Equip_History SET F_Notice_Email = 0

WHERE F_Notice_Email = 1 and F_Notice_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and F_Sent_Date is null

END

DROP TABLE #EMails

--發準備信

SET @mailaddr = 'nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '請開始準備校驗信中所列儀器'

if object_id('#EMails2') is not null

begin

drop table #EMails2

end

SELECT

RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails2

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Prepare_Email = 1 and a.F_Prepare_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails2)

IF @MaxRownum > 0

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails2)

SET @msg = '清單所列儀器請開始準備校驗工作' + char(13) + char(10)+ char(13) + char(10)

set @msg=''+@msg+' '

WHILE @Iter <= @MaxRownum

BEGIN

FROM #EMails2

WHERE RowNum = @Iter) + char(13) + char(10) + ' '

SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + ' ' + ' 共' + cast(@MaxRownum as varchar) + '項'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr,

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;

UPDATE T_EMS_Equip_History SET F_Prepare_Email = 0

WHERE F_Prepare_Email = 1 and F_Prepare_Date <= CONVERT(VARCHAR(8) , GETDATE(), 112 ) and F_Sent_Date is null

END

DROP TABLE #EMails2

--發到期信

SET @mailaddr = 'nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '[重要]信中所列儀器今天已經到期請校驗'

if object_id('#EMails3') is not null

begin

drop table #EMails3

end

SELECT

RowNum = ROW_NUMBER() OVER(ORDER BY a.F_ID)

,a.*,b.F_Equip_Name,b.F_filename

INTO #EMails3

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Cali_Date = CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails3)

IF @MaxRownum > 0

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails3)

SET @msg = '清單所列儀器今天到期請校驗' + char(13) + char(10)+ char(13) + char(10)

set @msg=''+@msg+' '

WHILE @Iter <= @MaxRownum

BEGIN

SET @msg = @msg + (SELECT '應校驗日期:'+F_Cali_Date+' 編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+(CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">' as mg

FROM #EMails3

WHERE RowNum = @Iter) + char(13) + char(10)+ ' '

SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10)+ ' ' + ' 共' + cast(@MaxRownum as varchar) + '項'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr,

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;

END

DROP TABLE #EMails3

--發逾期信

SET @mailaddr = 'a@goldjoint.com.tw;nick.l@goldjoint.com.tw'

--SET @mailaddr = 'nick.l@goldjoint.com.tw'

SET @msg_subject = '[緊急]信中所列儀器已經逾期請速校驗'

if object_id('#EMails4') is not null

begin

drop table #EMails4

end

SELECT

RowNum = ROW_NUMBER() OVER(ORDER BY a.F_Cali_Date)

,a.*,b.F_Equip_Name,b.F_filename,DateDiff(d, cast(a.F_Cali_Date as datetime),GETDATE()) as Delay_Date

INTO #EMails4

FROM T_EMS_Equip_History a left join T_EMS_Equipment b on a.F_ID=b.F_ID

WHERE a.F_Cali_Date < CONVERT(VARCHAR(8) , GETDATE(), 112 ) and a.F_Sent_Date is null

SET @MaxRownum = (SELECT MAX(RowNum) FROM #EMails4)

IF @MaxRownum > 0

Begin

SET @Iter = (SELECT MIN(RowNum) FROM #EMails4)

SET @msg = '清單所列儀器已經逾期請速校驗' + char(13) + char(10)+ char(13) + char(10)

set @msg=''+@msg+' '

WHILE @Iter <= @MaxRownum

BEGIN

SET @msg = @msg + (SELECT '應校驗日期:'+F_Cali_Date+' 逾期天數:'+cast(Delay_Date as varchar)+' 編號:'+F_ID+'_'+(CASE WHEN F_Equip_Name IS NULL THEN '沒有名稱' ELSE F_Equip_Name END)+ '<img src="file://192.168.2.231/GJERP$/品管部/儀器/'+ (CASE WHEN F_filename IS NULL THEN 'NONE.png' ELSE F_filename END) +'" height="100" width="100">' as mg

FROM #EMails4

WHERE RowNum = @Iter) + char(13) + char(10) +' '

SET @Iter = @Iter + 1

END

SET @msg = @msg + char(13) + char(10) + ' ' + ' 共' + cast(@MaxRownum as varchar) + '項'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'NickAutoMail',

@recipients = @mailaddr,

@subject = @msg_subject,

@body_format=HTML,

@body = @msg;

END

DROP TABLE #EMails4

奇怪ㄟ你!

2013年5月6日星期一

Database Mail的範例

沒有留言:

張貼留言

2013年5月6日 星期一

Database Mail的範例

沒有留言:

張貼留言

2013年5月6日星期一