Automatic Emails from a query -Unique records only

Question

Post reply

Automatic Emails from a query -Unique records only

leemunyasia

SSC Veteran

Points: 235
More actions
November 24, 2011 at 2:06 am

#247392

Hi Guys,
I’m somewhat a novice on this issue, basically we would like to select specific records from one table using SQL, but, what we would like to do is send this information automatically, each time an order is registered with a specific criteria, SQL generates and ascii flat file and emails this to a user, we are not running on SQL 2005 enterprise so i was hoping to get the query correct as I only want unique records each time the mail is sent
Kindly advise:
Current query: unfortunately, each time this runs it will give both new and old records, we only require the new record created.
select * from orders
where contract = Null
Lee

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

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

Send the e-mail from within a trigger.

Use the INSERTED logical table to capture the new records only.

Hope this helps

Gianluca

-- Gianluca Sartori

jnuqui Default port Points: 1475 More actions · Answer 2

hmm is this even possible with older version of SQL like 2000?

anyway, the first suggestion is good, but i think ill go for trigger on insert. so every time there is a new record it will email directly.

1.trigger on insert will go to

email script to email user.

there's also a way to send email with HTML using t sql scripts.. 😀

link below for your reference..

http://qa.sqlservercentral.com/blogs/robert_davis/archive/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML.aspx%5D"> http://qa.sqlservercentral.com/blogs/robert_davis/archive/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML.aspx%5D

something about triggers ...

http://www.sql-server-performance.com/2004/triggers-2000%5D%5B/url%5D

good luck! cheers 🙂

===============================================================

"lets do amazing" our company motto..

leemunyasia SSC Veteran Points: 235 More actions · Answer 3

Thanks for the fast replies:

issue:

- we are not to use triggers on the database ever(company policy)

If i could maybe set this up in a jobb tjobchecks the query that each time it runs, I get the records I need it would be much appreciated.

kind regards

Lee

Ant-Green SSC Guru Points: 112965 More actions · Answer 4

is the insert of the order done by a stored proc?

you could amend the logic in the proc so that if the parameters meet the criteria then send a mail

if its inline sql then the only option I know of would be a trigger on insert

jnuqui Default port Points: 1475 More actions · Answer 5

OK so heres the catch. put the email alert on the same stored procedure your application is using to insert the orders. with this it will surely mail the new order to you.

using the job to monitor it per second is the same as using triggers. it eats resources.. 😀

===============================================================

"lets do amazing" our company motto..