November 24, 2011 at 2:06 am
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
November 24, 2011 at 2:16 am
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
November 24, 2011 at 2:28 am
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://www.sqlservercentral.com/blogs/robert_davis/archive/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML.aspx%5D"> http://www.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..
November 24, 2011 at 2:57 am
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
November 24, 2011 at 4:19 am
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
November 24, 2011 at 7:26 am
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..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply