June 11, 2007 at 2:10 pm
I have the following query that will send me an e-mail with the query results in the message body. What I would like to do is only send me an e-mail if the results of the query are over a certain value. Does anyone know how to do this. Below is my procedure and query.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'emailaddress',
@body='The IP address with over 200 hits are listed below',
@importance ='High',
@subject ='tbl_timetracker over 20',
@profile_name ='mailprofile',
@query ='Select ip, Count(tbl_timetracker.ip) As Total
from tbl_timetracker
where datediff(day,getdate(),date)=0 and date <= getdate()
Group BY term,IP
Having COUNT(ip) > 20';
June 11, 2007 at 2:37 pm
The most straightforward way would be to create a global temp table based on your query, then evaluate your result and send email only if it is true
June 11, 2007 at 2:41 pm
I thought about that but my problem will remain the same. What is the code to send an email if my query results are true?
June 13, 2007 at 6:44 pm
1. Create global temp. table:
Select ip, Count(tbl_timetracker.ip) As Total
into ##result
from tbl_timetracker
where datediff(day,getdate(),date)=0 and date <= getdate()
Group BY term,IP
Having COUNT(ip) > 20
2. Evaluate result table ##result based on your requirements
3. If it is TRUE send email:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'emailaddress',
@body='The IP address with over 200 hits are listed below',
@importance ='High',
@subject ='tbl_timetracker over 20',
@profile_name ='mailprofile',
@query ='select * from ##result';
4. Drop table ##result
August 22, 2011 at 9:08 am
Hi,
I try on this query, but how can I worked on the condition.
Nomatter what is the where condition, it seems like sending me notification email.
How can I apply to send notification email if only it hit the where condition?
August 22, 2011 at 9:16 am
i think you want to use IF EXISTS:
IF EXISTS (select * from ##result)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'emailaddress',
@body='The IP address with over 200 hits are listed below',
@importance ='High',
@subject ='tbl_timetracker over 20',
@profile_name ='mailprofile',
@query ='select * from ##result';
END
Lowell
August 22, 2011 at 9:20 am
i received an error:
Invalid object name '##results'.
August 22, 2011 at 9:25 am
girl_bj0619 (8/22/2011)
i received an error:Invalid object name '##results'.
the examples above (admittedly they are from 2007) suggested using a global temp table. i base dmy example fof of the previous posts.
you'll have to adjust the code to your real situation, but the test is the same:
--
IF EXISTS(SELECT FROM YOUR TABLE WHERE SOMeColumn = SomeValue)
BEGIN
--do stuff like sending emails.
PRINT 'Emails Sent'
END
Lowell
August 22, 2011 at 9:39 am
Thanks!
Works perfectly now 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply