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
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...
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
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’! **
below86
below86
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 2130
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




-------------------------------------------------------------
we travel not to escape life but for life not to escape us
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 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



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