Subquery returned more than 1 value

Question

I need to set up a job in SQL Server 2008 to run at the first of the month sending an email to our customers. However, I do not know how to loop through the results of the subquery.

Resulting in this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here's the relevant code:

SET @Recipients =(SELECT DISTINCT a.EMail
         FROM   a
        --approximately 600 email addresses 

SET @MailSubject = 'Customer News' 
SET @MailRecipients = @Recipients
SET @MailMessage =  'Dear customer, Attached is your customer news letter.'

SET @FileName = N'E:file\to\be\attached.doc'

EXEC msdb.dbo.sp_send_dbmail @recipients = @MailRecipients, 
    @body = @MailMessage,
    @blind_copy_recipients='[e-mail]', 
    @subject = @MailSubject,
    @file_attachments  = @FileName

Answer 1

the problem is @recipients = @MailRecipients. It is expecting a string (an email address) and you are giving it a recordSet.

Answer 2

The error is here where you have many rows trying to be assigned to a single variable

SET @Recipients =(SELECT DISTINCT a.EMail
     FROM   a
    --approximately 600 email addresses 

You'd need to change it to a separated list thus

SET @Recipients = STUFF(
           (select DISTINCT ';' + CAST(a.EMail AS varchar(max))
           FROM a FOR XML PATH ('')
           )
          ,1,1, '') 

Note: @Recipients will need to be varchar(max)