Hello again experts, this is part an ongoing effort to get the stored procedure to work correctly.
I have One known issue I would like your assistance with.
1, The issue is that, when there are more than one records where IsOpen=1, I expect two emails to go out. Instead, only one record goes out eve though the two records are eventually updated with IsOpen being set 1 where they were previously NULL or 0.
I know that part of the reason is because of this:
select @email=email,...
If I change the code to this:
select @email=@email +';'+email,... then everyone on the list gets the same email and everyone sees everyone else's email which is not a good idea.
Any ideas how to resolve this?
Code is below and as usual, thanks for your help.
ALTER proc [dbo].[EmailUpdatedRecords] as
DECLARE MAIL_CURSOR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT [ID]
,[Department]
,[Bldg]
,[DateCreated]
,[RequestID]
,[Email]
,[SubmittedBy]
FROM FeedBack
WHERE Status != 1
Declare @mailid int
declare @dept nvarchar(200)
declare @loc nvarchar(200)
declare @dteCreated nvarchar(20)
declare @RequestID nvarchar(12)
declare @email nvarchar(200)
declare @authorizedname nvarchar(200)
declare @message nvarchar(1000)
OPEN MAIL_CURSOR
FETCH MAIL_CURSOR into @mailid,@dept,@loc,@dteCreated,@RequestID,@email,@authorizedname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @email = email, @mailid=ID, @message = @message, @RequestID = RequestID,@authorizedname=SUBSTRING(SubmittedBy, CHARINDEX(',', SubmittedBy) + 1, LEN(SubmittedBy) - CHARINDEX(',', SubmittedBy) + 1)
+ ' ' + SUBSTRING(SubmittedBy, 1, CHARINDEX(',', SubmittedBy) - 1),
@loc=Bldg, @dtecreated=DateCreated, @dept=Department
FROM Feedback
WHERE email=@email
AND (lsOpen IS NULL or lsOpen = 0)
select @message = 'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.
Dear '+ @authorizedname +':
Thank you for using the order processing system.
Please click the link below to complete a survey
http://feedbackSurvey.php?rID=' +@RequestID+'&loc='+Replace(@loc,' ', '%20')+'&dept='+Replace(@dept,' ', '%20')+'
Regards,
Order administrator.'
--exec sp_send_cdontsmail @mail1, null,null,@content1,null
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Customer Feedback Survey', -- your defined email profile or static info
@recipients = @email, -- your email
--@blind_copy_recipients = @email,
@subject = 'Your feedback is needed',
@body = @message
--Update the record in ClosedRequest table where Sent = 'No'.
Update Feedback SET lsOpen = 1 WHERE lsOpen != 1 and ID = @mailid
FETCH MAIL_CURSOR INTO @mailid,@dept,@loc,@dteCreated,@RequestID,@email,@authorizedname
END
CLOSE MAIL_CURSOR
DEALLOCATE MAIL_CURSOR