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 Friday, September 6, 2013 5:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 1,076, Visits: 3,051
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...
Post #1492176
Posted Friday, September 6, 2013 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 2,449, Visits: 2,993
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.


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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:47 AM
Points: 308, Visits: 1,142
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
--email
End

-- If you are wanting the number of records to not be zero
If ((select count(*) from #results) <> 0)
Begin
--email
End


Post #1492240
Posted Friday, September 6, 2013 11:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 1,076, Visits: 3,051
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


Post #1492472
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse