SQLTeam.com | Weblogs | Forums

Stored proc sends email regardless of whether IsOpen is True or Not


#1

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

#2

Put print statements just before the sp_send_dbmail to see what you are handing over to sp_send_dbmail. Like this:

PRINT @email;
PRINT @message;
EXEC msdb.dbo.sp_send_dbmail
-- etc

If it is null then the mail wouldn't get sent.

Also, after your update statement, put a print statement to see how many rows were affected.

Update Feedback SET lsOpen = 1 WHERE lsOpen != 1 and ID = @mailid
print @@ROWCOUNT;

#3

Hi James,

Sorry for late response. I didn't even know there was a response.

I usually get an email when there is a response to my question. This time, I didn't.

So, I did add
PRINT @email;
PRINT @message;

Two strange things are happening.

I did this a few times.

Assume that I have two records where IsOpen !=1, when I execute the stored proc, sometimes it shows that two messages were processed.

In each instance, only one email was sent.

Second, I added the PRINT @@ROWCOUNT but it shows 1 update when one record displays; two when two records are updated.


#4

Why not just do blind copies?

Also note that in general, don't write SELECT @foo = @foo + ...

this is not supported (even though it often works). You can get incorrect results. Better to use SET instead of SELECT here or use FOR XML PATH('')


#5

You need to cursor through the detail data, not the upper-level selection of emails. Something like below:

ALTER PROCEDURE [dbo].[EmailUpdatedRecords]
AS
SET NOCOUNT ON;

DECLARE MAIL_CURSOR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR  
    SELECT  email, 
            ID, 
            message, 
            RequestID, 
            SUBSTRING(SubmittedBy, CHARINDEX(',', SubmittedBy) + 1, LEN(SubmittedBy) - CHARINDEX(',', SubmittedBy) + 1) + ' ' + SUBSTRING(SubmittedBy, 1, CHARINDEX(',', SubmittedBy) - 1) AS authorizedname,
	        Bldg, 
	        DateCreated, 
	        Department
	FROM   Feedback
	WHERE email IN (
            SELECT DISTINCT [Email]
	        FROM FeedBack
            WHERE Status != 1 AND Email > ''
          ) AND
         (lsOpen IS NULL or lsOpen = 0)
    ORDER BY email

declare @email nvarchar(200)
Declare @mailid int 
declare @message nvarchar(1000)
declare @RequestID nvarchar(12)
declare @authorizedname nvarchar(200) 
declare @bldg nvarchar(200)
declare @dateCreated nvarchar(20)
declare @department nvarchar(200)
 
OPEN MAIL_CURSOR
 
WHILE 1 = 1
BEGIN
    FETCH MAIL_CURSOR into @email, @mailid, @message, @RequestID, @authorizedname, @bldg, @dateCreated, @department
    
    IF @@FETCH_STATUS <> 0
        BREAK;
        
    SELECT 
        @mailid = ISNULL(@mailid, 0),
        @RequestID = ISNULL(@RequestID, 0),
        @authorizedname = ISNULL(@authorizedname, ''),
        @bldg = ISNULL(@bldg, ''),
        @dateCreated = ISNULL(@dateCreated, 0),
        @department = ISNULL(@department, '')  

    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(@bldg,' ', '%20')+'&dept='+Replace(@department,' ', '%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 Feedback 
    SET lsOpen = 1 
    WHERE ID = @mailid AND (lsOpen IS NULL OR lsOpen != 1)

END

DEALLOCATE MAIL_CURSOR

#6

Awesome, Scott.

Thanks so much - works great now.