Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Result in Email?


Query Result in Email?

Author
Message
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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'
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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



HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
Thanks for reply..

I want send email the same query result.. But I don't want send mail if Diff value=0
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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.
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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



HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search