December 9, 2008 at 1:23 pm
Hello All,
I need some help in creating a script that sends out an email message to a specific email address once I run a specific query with a condition.
So my query would be like to select all data returned from a query, put it in a temp table and do a row count against that temp table.. if NO records.. then generate email...
Will appreciate your help. Thanks
--
:hehe:
December 9, 2008 at 2:42 pm
We'd like to see you do some of the work here. Why do you need to drop things in a temp table? If there are no records, then you can use a IF NOT EXISTS (select xxx) statement.
As to the email, you need database mail enabled. Then it's a simple call to sp_send_dbmail
December 9, 2008 at 2:54 pm
Hey Steve,
Thanks for the sp_send_dbmail command. I actually logged back on to post that message because after some research I found out I could use that. I configured my database mail and sent out a sample query to my own email address and it works like a charm.
As far as the query for the stored procedure, I'm going to work on it tomorrow and try to post the solution here. Thanks for the help anyway though!
Regards,
Omair
--
:hehe:
December 10, 2008 at 10:57 am
Steve:
You mentioned the IF NOT EXISTS statement. I'm wondering if I can use it. But how would it work?
So I put my SELECT statement inside the IF NOT EXISTS statement and if the SELECT statement does not produce a result-set, how would the IF NOT EXIST handle this?
I guess I've never used the IF NOT EXISTS statement before so I'm abit confused. Is it mainly used for this purpose? that if a result-set does NOT produce any results than send the email to the concerned party?
Thanks,
Omair
--
:hehe:
December 10, 2008 at 11:03 am
Okay figured it out ;)... below is my final query:
IF NOT EXISTS(SELECT [Server] AS ActiveDirectoryServerName,
Enabled AS IsActiveDirectoryCommunicatingWithDocLink
FROM LDAPConfiguration
WHERE [Server] = 'OBI-WAN'
AND Enabled = 1)
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'Omairs Email',
@recipients='brubin@zaxbys.com',
@body=N'Active Directory is not communicating with Doc-Link'
ELSE PRINT 'The Active Directory server OBI-WIN is communicating with Doc-Link.'
Thanks Steve for your guidance!
--
:hehe:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy