dbmail puzzler
This had me scratching my head for a short while:-
The first query sends a mail with a return status of 0
exec @return = msdb.dbo.sp_send_dbmail @profile_name='Any profile Will Do',
@subject = @subject,
@body = @body,
@query = @query,
@query_result_separator = @separator,
@exclude_query_output = 1,
@recipients = 'colin.leversuch-roberts@xyz.co.uk;john.smith@abc.co.uk;jim.jones@abc.co.uk';
This query doesn’t send a mail but still returns a status of 0
exec @return = msdb.dbo.sp_send_dbmail @profile_name='Any profile Will Do',
@subject = @subject,
@body = @body,
@query = @query,
@query_result_separator = @separator,
@exclude_query_output = 1,
@recipients = 'colin.leversuch-roberts@xyz.co.uk;
john.smith@abc.co.uk;
jim.jones@abc.co.uk';
I had a few email addresses to send to, they’re a bit longer than these shown, and I only have a 15” monitor so I tend to drop code down a line so I can actually see it.
I’d tested the call using myself as recipient and it was only after adding the extra recipients the call stopped – but no error messages – well there was one in the log :-
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-08-01T08:58:17). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
Which was very useful, I cut and pasted the email addresses into exchange and sent a mail with no problem. Had me puzzled for a while.