SQL Server database mail problem
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.
Please help.
Thank you!!
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.
Please help.
Thank you!!
USE BurgoyneMusic
GO
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()))
USE msdb
EXEC sp_send_dbmail @profile_name='MyProfile',
@recipients= @Emails,
@subject='subject',
@body='message body'
Hi,
could you please do a "PRINT @Emails" just before the "USE msdb" to see what is the result of the query. It looks like you're not putting a separator between the email addresses. So you're probably getting something like: "email1@abc.comemail2@abc.
If you want to see the detailed error, you can look in the logging tables. Here's one:
SELECT * FROM msdb.dbo.sysmail_failedite
P.S. You don't need to change the database context with "USE msdb". You can prefix the call to sp_send_dbmail like this:
EXEC msdb..sp_send_dbmail @profile_name='MyProfile',
@recipients= @Emails,
@subject='subject',
@body='message body'
could you please do a "PRINT @Emails" just before the "USE msdb" to see what is the result of the query. It looks like you're not putting a separator between the email addresses. So you're probably getting something like: "email1@abc.comemail2@abc.
If you want to see the detailed error, you can look in the logging tables. Here's one:
SELECT * FROM msdb.dbo.sysmail_failedite
P.S. You don't need to change the database context with "USE msdb". You can prefix the call to sp_send_dbmail like this:
EXEC msdb..sp_send_dbmail @profile_name='MyProfile',
@recipients= @Emails,
@subject='subject',
@body='message body'
ASKER
Tro- I will try that when I get back to computer.
Pbarry- I designed my data to only return one row to start testing but I will try that too. And if I don't say "use msdb" then send dbmail is not declared.
Pbarry- I designed my data to only return one row to start testing but I will try that too. And if I don't say "use msdb" then send dbmail is not declared.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pbarry - I tried the ISNULL and it gave me the error, "the isnull property requires 2 arguments". But I did change the EXEC msdb.dbo.sp_....to what you said and it was fine. Still not getting anything for @Emails. I tried "print @Emails" and it did nothing. Then I checked the error log like you suggested and it did not have an error for my recent tries with the way it all is.
Tro- I tried your new select @Emails = "Select....etc. and with the quotes around the 2nd Select, it says my argument is too long. It has to be 128 charachters or less. If I do it without the quotes around the 2nd select, then it says incorrect syntax near Select.
Tro- I tried your new select @Emails = "Select....etc. and with the quotes around the 2nd Select, it says my argument is too long. It has to be 128 charachters or less. If I do it without the quotes around the 2nd select, then it says incorrect syntax near Select.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Initialize @Emails to empty string
----------------------
select @Emails=''
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,GETDA