I am using the below code in SQL Server 2008 to query the database and send an email to user(s) based on the criteria in my select statement.
Problem is, that the variable @Emails is not getting anything. It should get the email address or multiple email address of the query to send using database mail.
I know that the query is returning rows, because if I comment out the "USE msdb" and below, I get a table of results. When I uncomment that code, I get an error which says, "At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients"." So I know that it is not getting the @reciepents from the query.
Declare @Emails varchar(50)
Select @Emails=@Emails + (a.Email) From Student As a Where exists (SELECT * From CreditCard As b WHERE a.StudentID = b.StudentID And b.CCMonth = month(DATEADD("m",-1,GETDATE())) And b.CCYear = DATEPART(year,GETDATE()))
EXEC sp_send_dbmail @profile_name='MyProfile',