Solved

SQL Server database mail problem

Posted on 2010-09-15
6
1,384 Views
Last Modified: 2012-05-10
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!!
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'

Open in new window

0
Comment
Question by:mlg101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 33687209
null +'something' produce null
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,GETDATE())) And b.CCYear = DATEPART(year,GETDATE()))
0
 
LVL 4

Expert Comment

by:pbarry1
ID: 33687232
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.comemail3@abc.com ..." which is not a valid address.  

If you want to see the detailed error, you can look in the logging tables.  Here's one:
   SELECT * FROM msdb.dbo.sysmail_faileditems

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'
0
 
LVL 1

Author Comment

by:mlg101
ID: 33687314
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.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 4

Accepted Solution

by:
pbarry1 earned 250 total points
ID: 33687616
One more thing,  unless you're using the "SET CONCAT_NULL_YIELDS_NULL OFF;" setting, if your query returns a record with a NULL email address, you'll end up with a NULL string.  You should check for NULL email addresses and replace it with something you want:  For instance:

Select @Emails=@Emails + ISNULL(a.Email, '') From Student As a Where...

As for the "USE msdb", I'm not sure why you get an error message, but this one will work (adding the "dbo" schema):
EXEC msdb.dbo.sp_send_dbmail @profile_name='MyProfile',
@recipients= @Emails,
@subject='subject',
@body='message body'
0
 
LVL 1

Author Comment

by:mlg101
ID: 33688242
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.
0
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 250 total points
ID: 33688469
1) emails  must be separated by ","
2) isnull does need 2 arguments, make sure you copy all code as is.
3) "print did nothing" - confirms that @Emails value is null after your select. Some of a.emails are nulls
4) what it means "with the quotes around the 2nd Select..." Can you post statement that gives you error?

try this
-----------------------
Declare @Emails varchar(526)
Select @Emails=isnull(@Emails+',','') + a.Email From Student As a
Where a.Email>''
and 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()))

print '@Emails='+ isnull(@Emails, 'empty')
--USE msdb
EXEC msdb..sp_send_dbmail @profile_name='MyProfile',
@recipients= @Emails,
@subject='subject',
@body='message body'
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question