-1

I will need a steps that detect some values and alert me through email.

So I enable Database Email and setup.

I will need a Query that detects and if Value is what I am looking for then, I will need that to be send email alert.

SELECT * FROM Worksheet WHERE EffectiveDate > GETDATE() OR Status=5

I am only looking for Status 5 and if Effective Date time is less then -5 hours from the current pulling time.

Can anyone help me with this?

Thank you!

user2502947
  • 69
  • 1
  • 4
  • what exactly you need help with? With setting up the job? With the query? BTW, if you state "for Status 5 and if Effective Date time is less then -5 hours" then you need to replace OR operator with... AND. – Eduard Uta Dec 17 '14 at 14:58
  • I use OR because, I need Status=5 or EffectiveDate time -5.... – user2502947 Dec 17 '14 at 16:18

1 Answers1

1

With respect to your query:

SELECT * FROM Worksheet WHERE EffectiveDate > GETDATE() OR Status=5

should be

SELECT * FROM Worksheet WHERE EffectiveDate > DateAdd(h,-5,GetDate()) AND Status=5

As to how to send an email from an Agent job based on the result of a query I have no idea. I'd use SSIS if you have that available. You can run an Execute SQL task to execute your query, store the result in a variable, then execute a Send Mail task based on the presence or absence of data in your variable....

Richard Schaefer
  • 525
  • 3
  • 13
  • 45
  • EXEC HQ_Server_Test.dbo.sp_send_dbmail profile_name = 'Database_Mail', recipients = 'dryu@bevello.com', subject = 'Workshee Status', query = N'SELECT ID, Style, EffectiveDate, Status, Notes, Title, FromDate, DBTimeStamp FROM Worksheet WHERE Status=5', attach_query_result_as_file = 1, query_attachment_filename = 'Worksheet Status.txt' This how I made query in Steps but not working....anything wrong? – user2502947 Dec 17 '14 at 15:45
  • Hi Richard, I use sendEmail program to send but if there's no data, how can I stop sending email? – user2502947 Dec 17 '14 at 16:15
  • An IF-BEGIN-END block that checks the output of the query and sends the email if there's output? TSQL is pretty limited in terms of conditional logic and processing... – Richard Schaefer Dec 17 '14 at 19:07