November 27, 2007 at 11:44 am
I am totally missing something here and feel ridiculous but I need help.
I have a DTS package that I plan to run once a month. The first task is as follows:
----Delete the records out of the table Reminder
BEGIN
DELETE FROM Reminder
END
----Populate the table based on the following criteria
BEGIN
INSERT INTO Reminder (record_id, form_type, act_comp_date, resp_party, resp_party_email, subject)
SELECT record_id, form_type, act_comp_date, resp_party, resp_party_email, subject
FROM form_data
WHERE (act_comp_date IS NULL) AND (form_type = 'Preventive Action') OR
(act_comp_date IS NULL) AND (form_type = 'Corrective Action')
END
Now what I want to do is send an email message to each resp_party_email record. The problem is only the last record is getting an email. Am I missing some kind of loop command here to make each record get the email?
----This is what I am trying but only the last record is getting the email
DECLARE
@recordid varchar(10),
@formtype varchar(50),
@respparty varchar(75),
@resppartyemail varchar(75),
@subject varchar(50),
@mailSTR varchar(800)
BEGIN
Select @recordid=reminder.record_id,
@formtype=reminder.form_type,
@respparty=reminder.resp_party,
@resppartyemail=reminder.resp_party_email,
@subject=reminder.subject
from reminder
END
BEGIN
SELECT @mailSTR = 'd:\postie\postie -host:smtp.company.com -to:' + @resppartyemail + ' -s:"Open Action Update Reminder" -from:sysadmin@company.com -msg:"\r' + @respparty +' \r \rJust a reminder to submit an updated Work History for your Open ' + @formtype + ', record # ' + @recordid + ' - ' + @subject + ' - at least every 30 days.\r\rThank you, Quality Management" '
BEGIN
EXEC master..xp_cmdshell @mailSTR
END
END
Any eye opening fixes would be great!
Stacey
November 27, 2007 at 12:53 pm
A parameter can only have one value,
so right here
Select @recordid=reminder.record_id,
@formtype=reminder.form_type,
@respparty=reminder.resp_party,
@resppartyemail=reminder.resp_party_email,
@subject=reminder.subject
from reminder
the parameters can only hold the last value.
but you will either have to use a cursor or a looping structure because you want to do something for each row in the table.
so looping structure would look like this.
This solution requires a identity column on your reminder table. I called ReminderID
DECLARE
@recordid varchar(10),
@formtype varchar(50),
@respparty varchar(75),
@resppartyemail varchar(75),
@subject varchar(50),
@mailSTR varchar(800)
Declare @x int
declare @y int
select @x = min(ReminderID) from reminder
select @y = max(ReminderID) from reminder
while @x <= @y
BEGIN
Select @recordid=reminder.record_id,
@formtype=reminder.form_type,
@respparty=reminder.resp_party,
@resppartyemail=reminder.resp_party_email,
@subject=reminder.subject
from reminder
where ReminderID = @x
SELECT @mailSTR = 'd:\postie\postie -host:smtp.company.com -to:' + @resppartyemail + ' -s:"Open Action Update Reminder" -from:sysadmin@company.com -msg:"\r' + @respparty +' \r \rJust a reminder to submit an updated Work History for your Open ' + @formtype + ', record # ' + @recordid + ' - ' + @subject + ' - at least every 30 days.\r\rThank you, Quality Management" '
EXEC master..xp_cmdshell @mailSTR
set @x = @x + 1
END
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply