How do I send the result through an e-mail only if

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How do I send the result through an e-mail only if

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-10 : 13:47:35

I simple query return status and send out an e-mail only if the status is FAILED. How can I incorporate the result into a job and send out to the user every day. Any help would greatly appreciate.

The trick is only send out an e-mail if the status is failed otherwise don't send.

if objectproperty(object_id('Test1'), 'istable') = 1
DROP TABLE Test1
go

CREATE TABLE Test1
(
TransPostDate DATETIME NULL
);

INSERT Test1 Values ('11/06/2009');
go

SELECT CASE
WHEN (CONVERT(CHAR(8), TransPostDate, 112) = CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)) THEN 'ROLLED'
ELSE 'FAILED'
END AS 'Status'
FROM dbo.Test1;

-- Rule: Send an e-mail only if the status is FAILED.

-----------------------------------------------------------------------------

---testing...

DECLARE @status VARCHAR(25)

SET @Status = (SELECT CASE
WHEN (CONVERT(CHAR(8), TransPostDate, 112) = CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)) THEN 'ROLLED'
ELSE 'FAILED'
END AS 'Status'
FROM dbo.Test1);

EXEC master..xp_sendmail @recipients = 'user1@Mycompany.com',
@message = @Status,
@subject = 'Notification status.'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 13:54:47

if @status='FAILED'
begin
exec master..xp_sendmail ...
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources