Stored procedure not sending out emails using sp_send_dbmail

Question

Greetings again mates,

The following stored proc performs several different tasks at once.

At the top of the SP, the SELECT statement queries two tables (Employees and AuthorizedUsers) to pull names and email addresses of employees based on some WHERE predicate.

This works great.

The second part stores the data pulled from the tables above into another table called Notifications table.

This works great as well.

The third part of the task the SP does is to retrieve records from Notifications table where the value of a flag called IsSent = No.

Finally, the data pulled from Notifications table is sent to the email addresses also pulled from the Notifications tables and after the emails are sent, then IsSent is updated to Yes.

So far, almost everything works except that the emails are not being sent using sp_send_dbmail.

I am having difficulty figuring out the source of the problem.

Any assistance is greatly appreciated.

  CREATE PROCEDURE [dbo].[GetRegistrationInfo] 
  AS
  BEGIN
      DECLARE Register_Cursor CURSOR FOR 
      
     SELECT Name, COALESCE(NULLIF(PersonalEmail,''),WorkEmail) AS EmailAddress
     FROM AuthorizedUsers au
     WHERE NOT EXISTS (
           SELECT *
           FROM Employees e 
           WHERE e.email IN (au.work_email, au.home_email)
           ORDER BY Name ASC
  
      OPEN Register_Cursor
  
      DECLARE @fullname NVARCHAR(100)
      DECLARE @email NVARCHAR(MAX)

  
      -- Get the current MAX ID 
      DECLARE @mailID as INT 
  
      -- Start reading each record from the cursor. 
      FETCH Register_Cursor INTO @fullname, @email
  
      WHILE @@FETCH_STATUS = 0 
      BEGIN 
          INSERT INTO [Notifications] (mailContent, FullName, email_Addr, sender, IsSent) 
          VALUES ('This is a computer generated email message. 
  Please DO NOT use the REPLY button above to respond to this email. 
  
  Dear '+@FullName+': 
  
  Thanks for registering for the Training!
  
  Below are details of your registration information:
  
  Your UserName is: '+@email+'. 
  
  Your Password is: '12334r'. 
  
  Once you have retrieved your login information, please click the link below to get back to Training login screen and begin to begin to enjoy the benefits of membership. 
  
  
  http://servername/training/
  
  Regards, 
  The Registrations & Elections Office.', @FullName, @email, 'NoReply@serverdomain', 'No') 
  
          FETCH Register_Cursor INTO @FullName, @email
      END
  
      CLOSE Register_Cursor  
      DEALLOCATE Register_Cursor
  END
  
  BEGIN
      DECLARE MAIL_CURSOR CURSOR FOR
          SELECT mailid, sender, mailcontent
          FROM [Notifications]
          WHERE IsSent = 'No'
  
      DECLARE @mail1 INT
      DECLARE @sender NVARCHAR(100)
      DECLARE @content1 NVARCHAR(4000) 
  
      OPEN MAIL_CURSOR
  
      FETCH MAIL_CURSOR INTO @mail1, @sender, @content1
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SELECT @email = @email + ';' + Email
          FROM [Notifications]
          WHERE IsSent = 'No'
  
          -- exec sp_send_cdontsmail @mail1, null,null,@content1,null
          EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = 'Elections_Office', 
                    @recipients = @email, 
                    @subject = 'Your Account Details',
                    @body = @content1;
  
          -- Update the record in Notifications table where IsSent = 'No'.
          UPDATE [Notifications] 
          SET IsSent = 'Yes' 
          WHERE IsSent = 'No' AND mailid = @mail1
  
          FETCH MAIL_CURSOR INTO @mail1, @sender, @content1
      END
  
      CLOSE MAIL_CURSOR
      DEALLOCATE MAIL_CURSOR
  END

I believe my issue lies in this snip:

WHILE @@FETCH_STATUS = 0     
 BEGIN          
SELECT @email = @email + ';' + Email         
 FROM [Notifications]          
 WHERE IsSent = 'No'            
-- exec sp_send_cdontsmail @mail1, null,null,@content1,null         
 EXEC msdb.dbo.sp_send_dbmail                   
 @profile_name = 'Elections_Office',                     
@recipients = @email,                     
@subject = 'Your Account Details',           

Thanks in advance for your assistance

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

The issue is that you are overwriting the @email variable in each iteration of the loop, meaning it only contains the email addresses from the last record in the cursor.

I would build a string that contains all the email addresses separated by semicolons and then use that string as the recipient list for sp_send_dbmail.

Answer 2

Note that the loop which fills in Notifications is not need. You can replace this with a single INSERT:

      INSERT INTO [Notifications] (mailContent, FullName, email_Addr, sender, IsSent) 
          SELECT  'This is a computer generated email message. 
  Please DO NOT use the REPLY button above to respond to this email. 
  
  Dear '+ au.FullName +': 
  
  Thanks for registering for the Training!
  
  Below are details of your registration information:
  
  Your UserName is: ' + au.email +'. 
  
  Your Password is: '12334r'. 
  
  Once you have retrieved your login information, please click the link below to get back to Training login screen and begin to begin to enjoy the benefits of membership. 
  
  
  http://servername/training/
  
  Regards, 
  The Registrations & Elections Office.', au,FullName, au.email, 'NoReply@serverdomain', 'No'
   FROM AuthorizedUsers au
     WHERE NOT EXISTS (
           SELECT *
           FROM Employees e 
           WHERE e.email IN (au.work_email, au.home_email)

For sending the mail, you need a loop, since the text for each recipient is different. But you are populating the @email variable in the wrong way. It should be populated in the FETCH statement, and the email address should be added to the SELECT that drives the cursor. What happens now is that @mail is NULL after the previous loop and remains NULL

Also, the call to sp_send_dbmail and the UPDATE should be wrapped in a transaction, to make sure that both things are performed and not only one of them.

Answer 3

Hi @Simflex

Please check this untested query:

CREATE PROCEDURE [dbo].[GetRegistrationInfo] 
AS
BEGIN
  BEGIN
  DECLARE Register_Cursor CURSOR FOR 
  SELECT Name, COALESCE(NULLIF(PersonalEmail,''),WorkEmail) AS EmailAddress
  FROM AuthorizedUsers au
  WHERE NOT EXISTS (SELECT * FROM Employees e WHERE e.email IN (au.work_email, au.home_email) )
  ORDER BY Name ASC
  
  OPEN Register_Cursor
  DECLARE @fullname NVARCHAR(100)
  DECLARE @email NVARCHAR(MAX)
-- Get the current MAX ID 
  DECLARE @mailID INT
  SELECT @mailID = MAX(mailid) FROM [Notifications]
  
  -- Start reading each record from the cursor. 
  FETCH NEXT FROM Register_Cursor INTO @fullname, @email
  WHILE @@FETCH_STATUS = 0 
    BEGIN 
    SET @mailID = @mailID + 1
	INSERT INTO [Notifications] (mailid, mailContent, FullName, email_Addr, sender, IsSent) 
    VALUES (@mailID,
	'This is a computer generated email message. 
    Please DO NOT use the REPLY button above to respond to this email. 
    Dear '+@FullName+': 
    Thanks for registering for the Training!
    Below are details of your registration information:
    Your UserName is: '+@email+'. 
    Your Password is: '12334r'. 
    Once you have retrieved your login information, please click the link below to get back to Training login screen and begin to begin to enjoy the benefits of membership. 
    http://servername/training/
    Regards, 
    The Registrations & Elections Office.', @FullName, @email, 'NoReply@serverdomain', 'No') 
    
    FETCH NEXT FROM Register_Cursor INTO @FullName, @email
    END
    CLOSE Register_Cursor  
    DEALLOCATE Register_Cursor
  END
  
  BEGIN
    DECLARE MAIL_CURSOR CURSOR FOR
    SELECT mailid, email_Addr, sender, mailcontent
    FROM [Notifications]
    WHERE IsSent = 'No'
	ORDER BY mailid
  
    DECLARE @mail1 INT
    DECLARE @sender NVARCHAR(100)
    DECLARE @content1 NVARCHAR(4000) 
    DECLARE @mail_address NVARCHAR(MAX)

    OPEN MAIL_CURSOR
    FETCH NEXT FROM MAIL_CURSOR INTO @mail1, @mail_address, @sender, @content1
    WHILE @@FETCH_STATUS = 0
    BEGIN
      -- exec sp_send_cdontsmail @mail1, null,null,@content1,null
      EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'Elections_Office', 
           @recipients = @mail_address, 
           @subject = 'Your Account Details',
           @body = @content1;
  
      -- Update the record in Notifications table where IsSent = 'No'.
      UPDATE [Notifications] 
      SET IsSent = 'Yes' 
      WHERE IsSent = 'No' AND mailid = @mail1
  
      FETCH NEXT FROM MAIL_CURSOR INTO @mail1, @sender, @content1
    END
    CLOSE MAIL_CURSOR
    DEALLOCATE MAIL_CURSOR
  END
END

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.