Stored procedure not sending out emails using sp_send_dbmail

Simflex 301 Reputation points
2023-11-04T16:50:41.1533333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,136 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 20,095 Reputation points
    2023-11-04T17:03:41.25+00:00

    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.

    0 comments No comments

  2. Erland Sommarskog 104.3K Reputation points MVP
    2023-11-04T21:14:19.6633333+00:00

    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.

    0 comments No comments

  3. LiHongMSFT-4306 25,006 Reputation points
    2023-11-06T02:18:29.95+00:00

    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.

    0 comments No comments