April 17, 2005 at 6:03 pm
I need to check 1400 records every 24hrs for a condition (start date is within 14 days of current date) and if the condition is true, send an email using the information from the record. Then update the record that the email was sent. I am not sure what I should use to do this. SQL Jobs, notification services, something else???
April 19, 2005 at 2:17 am
This is a little script that creates a table, inserts some rows, then does the checking, emailing and updating you described. Obviously you will need to replace the table name and fields with your own and also ensure that the SQL Mail client is installed correctly and running. It basically attaches all the records that match the criteria and emails them to someone. Obviously if you need to send individual emails for each record then this script will not do.
Let me know if this is the case.
CREATE TABLE t
(
Field1 varchar(10),
StartDate datetime,
EmailSent bit
)
INSERT INTO t (Field1, StartDate, EmailSent) VALUES ('Record1', '2005-04-25', 0)
INSERT INTO t (Field1, StartDate, EmailSent) VALUES ('Record2', '2005-04-30', 0)
INSERT INTO t (Field1, StartDate, EmailSent) VALUES ('Record3', '2005-05-01', 0)
INSERT INTO t (Field1, StartDate, EmailSent) VALUES ('Record4', '2005-05-05', 0)
IF EXISTS (SELECT TOP 1 * FROM t WHERE EmailSent = 0 AND StartDate <= DATEADD(day, 14, GETDATE()))
BEGIN
EXEC master.dbo.xp_sendmail 'me@my.com', @query = 'SELECT Field1 FROM t WHERE EmailSent = 0 AND StartDate <= DATEADD(day, 14, GETDATE())'
UPDATE t SET
EmailSent = 1
WHERE EmailSent = 0 AND StartDate <= DATEADD(day, 14, GETDATE())
END
SELECT * FROM t
April 19, 2005 at 9:51 am
cool thatnks for the script. i dont need to be creating a table though i just need to be checking existing records, and the key is that i need to do it every 24hrs. So should i use SQL Jobs or something else???
April 19, 2005 at 9:55 am
lol. Yes, I only put the create table and record stuff in to illustrate the point. As for scheduling, just put the script in a SQL Agent scheduled job to run once a day at a quiet time, prefarably out of hours.
Check BOL if you need a reference on how to do it.
April 19, 2005 at 10:39 am
thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply