SQL Server Database mail fails 'a recipient must be specified'

Question

Post reply

SQL Server Database mail fails 'a recipient must be specified'

jcvv76

SSC-Addicted

Points: 485
More actions
October 6, 2016 at 7:53 am

#314094

Hi,
I'm using SQL Server 2008 R2 SP2 Standard to send email using msdb.dbo.sp_send_dbmail stored procedure, the problem is that sending mail fails with exception 'a recipient must be specified', but querying dbo.sysmail_faileditems I find all items have a recipient specified in the recipients column. Any suggestions?
The command I'm using (data isn't real, just in case):
--Inside a cursor iteration:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SomeProfile',
@recipients = 'some@mail.com',
@subject = 'Subject',
@body = 'some html',
@body_format = 'HTML';
The query to see the error message:
select mailitem_id, description
from dbo.sysmail_log
The result:
mailitem_id | description
515 | xxx 'a recipient must be specified'
The query to see the mail item:
select mailitem_id, recipients, sent_status
from dbo.sysmail_faileditems;
The result:
mailitem_id | recipients | sent_status
515 | some@mail.com| failed
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7686 More actions · Answer 1

Hi, are you running a query similar to this one and still getting the recipients error message?

USE msdb

GO

EXEC sp_send_dbmail @profile_name='DBServerAlerts',

@recipients='some.user@acme.com',

@subject='Test message',

@body='Congrats, Database Mail received by you Successfully.'

Sorry, I should have read your thread entirely. What happens if you run just the code above without the cursor iteration? Get the same error?

jcvv76 SSC-Addicted Points: 485 More actions · Answer 2

RVSC48 (10/6/2016)
Hi, are you running a query similar to this one and still getting the recipients error message?
USE msdb
GO
EXEC sp_send_dbmail @profile_name='DBServerAlerts',
@recipients='some.user@acme.com',
@subject='Test message',
@body='Congrats, Database Mail received by you Successfully.'
Sorry, I should have read your thread entirely. What happens if you run just the code above without the cursor iteration? Get the same error?

When iterating, one or two emails are sent, not always the error occurs. Thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

jcvv76 (10/6/2016)
RVSC48 (10/6/2016)
Hi, are you running a query similar to this one and still getting the recipients error message?
USE msdb
GO
EXEC sp_send_dbmail @profile_name='DBServerAlerts',
@recipients='some.user@acme.com',
@subject='Test message',
@body='Congrats, Database Mail received by you Successfully.'
Sorry, I should have read your thread entirely. What happens if you run just the code above without the cursor iteration? Get the same error?
When iterating, one or two emails are sent, not always the error occurs. Thanks.

So then possible it's an issue with the cursor. Since mail itself seems to be working fine, I would guess its more likely in the code, cursor or something in the process when you call dbmail.

Sue

jcvv76 SSC-Addicted Points: 485 More actions · Answer 4

Sue_H (10/6/2016)
jcvv76 (10/6/2016)
RVSC48 (10/6/2016)
Hi, are you running a query similar to this one and still getting the recipients error message?
USE msdb
GO
EXEC sp_send_dbmail @profile_name='DBServerAlerts',
@recipients='some.user@acme.com',
@subject='Test message',
@body='Congrats, Database Mail received by you Successfully.'
Sorry, I should have read your thread entirely. What happens if you run just the code above without the cursor iteration? Get the same error?
When iterating, one or two emails are sent, not always the error occurs. Thanks.
So then possible it's an issue with the cursor. Since mail itself seems to be working fine, I would guess its more likely in the code, cursor or something in the process when you call dbmail.
Sue

Hi Sue,

Thanks for your comment, but when I query the view msdb.dbo.sysmail_faileditems, the column recipients has a valid email address for all items, so it doesn't make sense the error message: 'a recipient must be specified'.

Juan

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Hi Sue,
Thanks for your comment, but when I query the view msdb.dbo.sysmail_faileditems, the column recipients has a valid email address for all items, so it doesn't make sense the error message: 'a recipient must be specified'.
Juan

A point worth considering is that it's what you believe to be a valid email address. I just created some failures with what appears to be valid email addresses.

I would try resending one of those emails using the same recipient outside of your code. Just a basic sp_send_dbmail

As a guess, you use a cursor to select from a table to get the email addresses used. That itself can introduce issues as well. I'm not sure I would focus so much on whats wrong with database mail as what could be an issue in the table being used, is there a pattern to the failures (certain users, domains, etc), is there a problem in the code, is something missed in error handling, etc.

Sue

jcvv76 SSC-Addicted Points: 485 More actions · Answer 6

Sue_H (10/6/2016)
Hi Sue,
Thanks for your comment, but when I query the view msdb.dbo.sysmail_faileditems, the column recipients has a valid email address for all items, so it doesn't make sense the error message: 'a recipient must be specified'.
Juan
A point worth considering is that it's what you believe to be a valid email address. I just created some failures with what appears to be valid email addresses.
I would try resending one of those emails using the same recipient outside of your code. Just a basic sp_send_dbmail
As a guess, you use a cursor to select from a table to get the email addresses used. That itself can introduce issues as well. I'm not sure I would focus so much on whats wrong with database mail as what could be an issue in the table being used, is there a pattern to the failures (certain users, domains, etc), is there a problem in the code, is something missed in error handling, etc.
Sue

I can't reveal the emails I'm using to proof they're valid, but the case is: an iteration with four records with the same email recipient, sometimes one is sent, somentimes two are, and the exception is always 'a recipient must be specified', that's why I'm not worried about the email addresses or the code. It may be just an issue with my particular instance, it may have something to do with the send by running on the cursor...

Thank you for your interest...

jcvv76 SSC-Addicted Points: 485 More actions · Answer 7

Sue_H (10/6/2016)
Hi Sue,
Thanks for your comment, but when I query the view msdb.dbo.sysmail_faileditems, the column recipients has a valid email address for all items, so it doesn't make sense the error message: 'a recipient must be specified'.
Juan
A point worth considering is that it's what you believe to be a valid email address. I just created some failures with what appears to be valid email addresses.
I would try resending one of those emails using the same recipient outside of your code. Just a basic sp_send_dbmail
As a guess, you use a cursor to select from a table to get the email addresses used. That itself can introduce issues as well. I'm not sure I would focus so much on whats wrong with database mail as what could be an issue in the table being used, is there a pattern to the failures (certain users, domains, etc), is there a problem in the code, is something missed in error handling, etc.
Sue

Hi Sue,

Considering your point, I've double checked email addresses and found two users with the same address, one of them ending with \r (carriage return).

Thanks a lot...

Juan

Sue_H SSC Guru Points: 90860 More actions · Answer 8

Glad it got resolved Juan - thanks for posting back the resolution.

Sue