Looking for how to get a database to E-Mail me based on data in SS2005

Question

Looking for how to get a database to E-Mail me based on data in SS2005

tnk

SSCrazy

Points: 2613
More actions
September 13, 2006 at 8:01 am

#119454

I have no idea if I am posting this in the right place, so I apologize if it is completely wrong. I am looking for a way to set up a specific functionality in a database and I don't even know what tool I should use yet.
I need to set up my SQL Server 2005 database to e-mail our marketing department when something happens. Specifically, I have an events table, and a news articles table. I only show future events on the website thats driven by this database. News articles are write-ups about events that happened. So my marketing person asked for an e-mail when an event passes. I store the date and time of all events. So when the date and time of an event is in the past, based on the current datetime, then I want the database to e-mail them of that fact, so they know to write a news article on the event.
If someone could point me in the right direction, as far as just what tool I should be looking into to use this, I would greatly appreciate it.
Neil

Viewing 3 posts - 1 through 2 (of 2 total)

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

alonzo SSC-Addicted Points: 443 More actions · Answer 1

One way of doing it is by writing a stored procedure similar to what I did below. I will then create a job that calls the stored procedure daily making sure that it is executed as the first step.

CREATE PROCEDURE pr_EmailEventsToWrite

AS

SELECT EventName into #tmp FROM EventsTable WHERE EventDate<getdate()

DECLARE @i INT,@Msg VARCHAR(8000),@bSendFlag bit

SET @bSendFlag=0

SET @i = (SELECT count(*) FROM #tmp)

IF @i > 0

SET @bSendFlag=1

SET @Msg='Start writing about the following events: '

WHILE (@i > 0)

BEGIN

DECLARE @eventname VARCHAR(50)

SELECT TOP 1 @eventname = EventName FROM #tmp

SET @Msg = @Msg+ CHAR(13)+ @eventname

DELETE #tmp WHERE EventName = @eventname

SET @i = (SELECT count(*) FROM #tmp)

END

IF @bSendFlag>0

EXEC master..xp_sendmail @recipients = 'aalvarado@aiisvc.com',

@subject = 'Events',

@message =@Msg

DROP table #tmp

tnk SSCrazy Points: 2613 More actions · Answer 2

Thanks a lot! I had to change some things because I am using SQL Server 2005 but it's roughly the same. I also just put my EXEC statement inside the If that checks if there are any rows in #tmp, that way I could get rid of the send flag altogether.