标题: TestDirector:How to send reminder through email notification [打印本页] 作者: pcl2004_27 时间: 2006-10-25 14:01 标题: TestDirector:How to send reminder through email notification Description
How to send emails based on time criteria and status running sp at the backend. Creat a job to exec stored procedure at every 1 hour
Solution
CREATE PROCEDURE TD_SCHEDULER
AS
DECLARE @BUGID INT, @BG_VTS DATETIME, @BUG_SEV VARCHAR(20),@BG_MAIL CHAR(1)
DECLARE Td_scheduler CURSOR FOR
SELECT BG_BUG_ID , DATEDIFF(hour,BG_VTS, getdate()), LEFT(BG_SEVERITY,1),BG_TO_MAIL
FROM td.BUG
WHERE BG_STATUS = 'New'
ORDER BY BG_BUG_ID
OPEN Td_scheduler
FETCH Td_scheduler
INTO @BUGID, @BG_VTS, @BUG_SEV, @BG_MAIL
WHILE @@FETCH_STATUS = 0
BEGIN
IF @BUG_SEV = 1 AND @BG_VTS > 2
update td.BUG
set BG_TO_MAIL = 'Y'
where BG_BUG_ID = @BUGID
else IF @BUG_SEV = 2 AND @BG_VTS > 4
update td.BUG
set BG_TO_MAIL = 'Y'
where BG_BUG_ID = @BUGID
else IF @BUG_SEV in (3,4,5) AND @BG_VTS > 24
update td.BUG
set BG_TO_MAIL = 'Y'
where BG_BUG_ID = @BUGID
FETCH NEXT FROM Td_scheduler
INTO @BUGID, @BG_VTS, @BUG_SEV,@BG_MAIL