SOLVED: SQL to email when table is updated.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SOLVED: SQL to email when table is updated.

Author

Topic

simpleton
Starting Member

25 Posts

Posted - 2007-08-07 : 12:41:55

I want to setup a method to get emailed whenever someone adds an item to a particular table.

I'm looking at SP's, and DTS's.. but i'm just not sure what the best path is.

http://www.sqldev.net/dts/SMTPTask.htm will create a great Custom DTS Email Task.. but then i don't know what to do next... I don't know how to say.. "If a new row is added, then run this DTS"... or if i should schedule the DTS to run every 30 minutes and then somehow query for new "Created" dates.

Please Help!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-07 : 13:14:30

You need insert trigger on the table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 13:34:24

Using a trigger to do this is highly not recommended since if the trigger breaks, it will cause issues.

If you have some column that will indicate a new row such as datetime with default of GETDATE(), then you can do this entire thing via a job that runs periodically. Do you have SQL Mail working?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

simpleton
Starting Member

25 Posts

Posted - 2007-08-07 : 14:02:22

I do not have SQL Mail working.. but i'm considering setting it up. I do have a CreatedDate column. I don't know how to setup the DTS to do this though...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 14:16:59

You don't need DTS for this. Do you have an Exchange server you can use and are you allowed to install Outlook on the database server? If so, you can setup SQL Mail. If not, then you can still use the SMTP stored procedure that uses CDO. That sproc can be found here if you search for it.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

simpleton
Starting Member

25 Posts

Posted - 2007-08-07 : 16:00:19

I know, i keep reading about it, but I just don't GET how to actually do it.
I actually have a column that I can query to see if i have any new items...

"select * from newrequest where assigned=0"

So, if this query has data in it, i want to get an email sent out.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 16:04:00

Both SQL Mail and CDO allow you to run queries and send the results via email. You could check for existence first then send email if any rows:

IF EXISTS (YourQueryGoesHere)
EXEC xp_sendmail ... <-- SQL Mail extended stored procedure

Tara Kizer
http://weblogs.sqlteam.com/tarad/

simpleton
Starting Member

25 Posts

Posted - 2007-08-09 : 13:26:25

Thanks for your help,
I'm all Setup and running smooth!
Here's what I ended up doing:

1. Installed DTS custom app/task: "SMTPTask.exe" http://www.sqldev.net/dts/SMTPTask.htm
2. Created DTS "MIS_EMAILER", added New SMTPTask, and customized it
3. Told the message body to pull from: D:\MISRequest_autoemail\New Ticket Export.txt
4. Created a SQL "TSQL" Job with the logic of: "If assigned=0 in newrequest table, then bcp a query out to "New Ticket.txt" file, and then run the DTS "MIS_EMAILER"
5. Scheduled this task to run every hour from 6am - 5pm


IF EXISTS (select * from newrequest where assigned=0) 
BEGIN declare @cmd varchar(1000) select @cmd = 'bcp "select workreq, submittedby, request1, comment FROM MISRequest..newrequest where assigned=0" queryout "D:\MISRequest_autoemail\New Ticket Export.txt" -T -c'
exec master..xp_cmdshell @cmd
exec master.dbo.xp_cmdshell 'dtsrun -E -S"ServerName" -N"MIS_EMAILER"'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources