September 5, 2013 at 12:09 am
Hi.
I have created database mail profile account with SMTP also email realy working.. Could you help me as below Query Result comes to Email?
Select A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff from
(select MACHINE, COUNT(*) as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3
Output
------
Machine
Instance
InstanceA
DIff
Thanks
ananda
September 5, 2013 at 12:34 am
The below code working fine... I want send mail when difference data apperaing.. output like below, if Difference data = 0 don't want send mail.
Machine-CM6
Instance -23
InstanceA -19
Diff -4
EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',
@recipients='ananad@xyz.com',
@subject='Alert Instance',
@query= 'Select A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*) as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3'
September 5, 2013 at 1:25 am
could anyone suggestion me as below coding condition matching or not? if diff value = 0
if NOT EXISTS (Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*)
as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3)
begin
EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',
@recipients='ananad.murugesan@ril.com',
@subject='Alert!-Verify LIMS Instance',
@query= 'Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*)
as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3'
end
September 5, 2013 at 1:35 am
You need to change the IF NOT EXISTS to IF EXISTS.
In the above query you check if results does not exists. If this is true (no results) you want to send and e-mail with the results of the same query (returning no results).
September 5, 2013 at 1:39 am
Thanks for reply..
I want send email the same query result.. But I don't want send mail if Diff value=0
September 5, 2013 at 1:52 am
If you apply the change I suggested, you won't be sending an e-mail when DIFF = 0. In the query you check if the DIFF is smaller then -3 or larger then 3. Hence the DIFF has to be unequal to 0 to comply to these filters. If DIFF equals to 0 then it won't comply to the filters in the query and this will result in the IF EXISTS clause to be false.
September 5, 2013 at 3:09 am
Ok. understand...
I changed EXISTS instead of NOT EXISTS..Mail not received because no rows selected that Query currently..
I have configured in SQL Jobs and run it every 5 mints then wait and monitor if mail received or not if rows selected.
September 6, 2013 at 5:36 am
Email not received from database even rows available as below query.. could suggestion me, what could be worng as below coding..I want receive email when rows available if no rows selected don't want send mail..
if EXISTS (Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*)
as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3)
begin
EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',
@recipients='ananad.murugesan@ril.com',
@subject='Alert!-Verify Instance',
@query= 'Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*)
as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3'
end
September 6, 2013 at 5:43 am
Execute the query below to get a list of all e-mails sent by the instance over the past 7 days. Look specific at the results in the "sent_status", "event_type" and "description" columns.
-- show a list of all mail-items
SELECT
sysmail_allitems.mailitem_id
, sent_status
, recipients
, subject, body
, send_request_date
, sysmail_event_log.event_type
, sysmail_event_log.description
FROM msdb.dbo.sysmail_allitems
LEFT OUTER JOIN msdb.dbo.sysmail_event_log
ON sysmail_allitems.mailitem_id = sysmail_event_log.mailitem_id
where send_request_date > dateadd(dd, -7, getdate())
order by
send_request_date desc
September 6, 2013 at 5:46 am
Do you recieve an e-mail if you execute the code below? I have taken the original code and removed the IF statement (so it will always execute sp_send_dbmail) and removed the WHERE filter of the query-statement (so it will return results).
EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',
@recipients='ananad.murugesan@ril.com',
@subject='Alert!-Verify Instance',
@query= 'Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*)
as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,
(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) B '
September 6, 2013 at 5:57 am
Email notifications working fine and there is no issues..also without if condition that script is working and received email alert from database..
I received lot of mail wheather rows selected or no rows selected that query...I want receive mail only rows selection, if no rows don't want receive mail...
September 6, 2013 at 6:13 am
ananda.murugesan (9/6/2013)
Email not received from database even rows available as below query..
ananda.murugesan (9/6/2013)
I received lot of mail wheather rows selected or no rows selected that query...
Above you have conflicting posts. This will make it hard to understand your problem and very difficult give you a good solution.
If you recieve too many e-mail then you have to look at the query inside the IF statement. Will this produce the results you expect for all different scenarios? Try to simulate this in a test-environment. Adjust the query if it needs modification. Copy/paste the final query as @query parameter if the query is working fine and the results are as expected.
If you need further help on this query you have to provide us with sample data that covers all situations.
September 6, 2013 at 7:43 am
It's realy hard to understand what you are after here is it the 'Diff' can not be zero to send email or if number of rows returned is not zero then send email?
This is just my problem with doing sub queries, so I broke it down to se what is going on.
select MACHINE, COUNT(*) as Instance
into #tableA
from PROD.dbo.CM_INSTANCE
group by MACHINE
Select COUNT(*)/4 as InstanceA
into #tableB
from PROD.dbo.CM_INSTANCE
Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff
into #results
from #tableA A,
#tableB B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3
-- If you are you trying to see if the 'Diff' is not zero to send email
If ((select Diff from #results) <> 0)
Begin
End
-- If you are wanting the number of records to not be zero
If ((select count(*) from #results) <> 0)
Begin
End
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 6, 2013 at 11:39 pm
Thanks for reply.. I have changed the script for email notification as below and sending email number of records not to be zero
select MACHINE, COUNT(*) as Instance
into #tableA
from PROD.dbo.CM_INSTANCE
group by MACHINE
Select COUNT(*)/4 as InstanceA
into #tableB
from PROD.dbo.CM_INSTANCE
Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff
into #results
from #tableA A,
#tableB B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3
-- If you are wanting the number of records to not be zero
If ((select count(*) from #results) <> 0)
Begin
EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',
@recipients='ananad.murugesan@ril.com',
@subject='Alert!-Verify Instance',
@query='select * from #results'
End
drop table #tableA
drop table #tableB
drop table #results
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply