SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

AskSQLTeam
Ask SQLTeam Question

USA
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?

Thanks,
Neil"

Kristen
Test

United Kingdom
22431 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

Kristen
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.09 seconds. Powered By: Snitz Forums 2000