title: Only few mails getting delivered using sp_send_dbmail in SQL Server 2008
body: <p>I am extracting the recipient list from a column in a table and I am running the stored procedure <code>sp_send_dbmail</code> to send the emails to these recipients. </p>
  
  <p>There are a total of around 200 recipients. </p>
  
  <p>Ironically only few of the mails are delivered even though the message that I am getting is <strong>Mail queued</strong>. The database mail is correctly configured and I am using the <code>Public</code> database profile. </p>
  
  <p>When I check the <code>msdb.dbo.sysmail_mailitems</code> table, as expected value in <code>sent_status</code> column is 1 for the recipients for whom the mail is delivered and for rest of them the value is either 0 or 2. </p>
  
  <p>I am completely sure that the recipient list is 100% correct. Do we have any workaround to resolve this issue?    </p>
  
  <p>Below is the code that I am running:  </p>
  
  <pre><code>CREATE procedure [dbo].[sp_dataRefreshNotification]  
  AS  
  BEGIN  
  DECLARE @ToMail VARCHAR(20)  
  DECLARE @Body1 VARCHAR(MAX) =   
  'Dear User,  
  Data has been refreshed.  
  Regards,  
  IT Support Team  
  
  Note: This is an auto generated e-mail, please do not reply this mail. '      
  
  SELECT DISTINCT RecipientAddress FROM dbo.RecipientAddressList  
  OPEN notification_cursor  
  FETCH NEXT FROM notification_cursor  
  INTO @ToMail  
  
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
  EXEC msdb.dbo.sp_send_dbmail  
  @profile_name  ='aaaa',    
  @Recipients = @Tomail,  
  @Subject = 'Required Data',      
  @Body = @Body1  
  FETCH NEXT FROM notification_cursor  
  INTO @ToMail  
  END  
  CLOSE notification_cursor  
  DEALLOCATE notification_cursor  
  END  
  </code></pre>