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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 xp_sendmail with a query
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 11/17/2005 :  07:44:19  Show Profile  Visit AskSQLTeam's Homepage
Neil writes "All,
I read your explanation of XP_Sendmail and it was very good. I support GreatPlains from Microsoft and the security in it is not the greatest. If you can see the data you have full control over it. We have some users who are inadvertantly deleting data. I would like to capture the data and email me with the deleted rows. I am using xp_sendmail and for the query I am using select * from DELETED. However SQL is returning the error that DELETED is an invalid object. Can xp_sendmail not access the temp table DELETED? If not can xp_sendmail send the calue of a variable?



United Kingdom
22859 Posts

Posted - 11/17/2005 :  08:30:59  Show Profile
GreatPlains is case sensitive isn't it? so you probably need "deleted"

"Can xp_sendmail not access the temp table DELETED?"

I don't suppose it will be in scope, no.

But either way this is a bad idea!

Stick the delete data into an "audit" table, and have a process that Emails you once an hour (say) if there are new rows in the table.

That avoids:

1) Timeout on the trigger (which may lock up the whole system, because there is a good chance that the table will be locked by the delete process at the time) when xp_sendmail has a fight with SMTP or your MX routing is having a Bad Hair Day.

2) You getting millions of emails when someone deletes the whole table

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000