Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Result in Email? Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 12:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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
Post #1491629
Posted Thursday, September 5, 2013 12:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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'
Post #1491630
Posted Thursday, September 5, 2013 1:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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


Post #1491646
Posted Thursday, September 5, 2013 1:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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).


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1491651
Posted Thursday, September 5, 2013 1:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
Thanks for reply..

I want send email the same query result.. But I don't want send mail if Diff value=0
Post #1491652
Posted Thursday, September 5, 2013 1:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1491659
Posted Thursday, September 5, 2013 3:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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.
Post #1491684
Posted Friday, September 6, 2013 5:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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


Post #1492173
Posted Friday, September 6, 2013 5:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1492174
Posted Friday, September 6, 2013 5:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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 '



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1492175
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse