SQL Script to send email

Question

SQL Script to send email

Slick84

SSCertifiable

Points: 5602
More actions
December 9, 2008 at 1:23 pm

#194855

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:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 1

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

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Slick84 SSCertifiable Points: 5602 More actions · Answer 2

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:

Slick84 SSCertifiable Points: 5602 More actions · Answer 3

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:

Slick84 SSCertifiable Points: 5602 More actions · Answer 4

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: