TestDirector:How to send reminder through email notification

pcl2004_27

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

END
CLOSE Td_scheduler
DEALLOCATE Td_scheduler