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:
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