msdb.dbo.sp_send_dbmail not sending email

Question

Post reply

msdb.dbo.sp_send_dbmail not sending email

happy55

Ten Centuries

Points: 1243
More actions
June 19, 2015 at 9:45 am

#398622

I am trying to execute the below query .Its says mail queued but no email is sent
exec msdb.dbo.sp_send_dbmail
@recipients = 'test@test.com',
@subject = 'test',
@body = 'Results are attached to this email',
@query= 'EXEC test,
@attach_query_result_as_file = 1 ,
@query_result_width = 32767,
@query_result_separator = ' ',
@query_attachment_filename='filename.txt'
When I remove the @attach_query_result_as_file and just execute below it emails fine.
exec msdb.dbo.sp_send_dbmail
@recipients = 'test@test.com',
@subject = 'test',
@body = 'Results are attached to this email',
@query= 'EXEC test
What is the issue when it tries to email as an attachment?
Thanks,

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'd bet the issue is unqualified database name;

@query= 'EXEC test,

needs to be soemthing that can be run form another database context , ie msdb or master

@query= 'EXEC SandBox.dbo.test', would probably work as expected.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

happy55 Ten Centuries Points: 1243 More actions · Answer 2

Thanks .It works .Is it same as using parameter @execute_query_database?

happy55 Ten Centuries Points: 1243 More actions · Answer 3

Its strange ,it worked first time and now not working again. The same query I am using .Is it something to do with my Microsoft exchange account ?

I have tried too many things but it doesn't works.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it doesn''t work is pretty vague;

It sounds like you are sending, but the mail doesn't go through; that's where you want to look at the mail logs and see what the specific error messages are

do you get results form the query below for the daterrange you were testing??

what is the specific error? attachment too large, for exmaple?

SELECT top 100

mail.send_request_date As SentDate,

sent_status As Reason,

err.[description] As SpecificError,

mail.*

FROM [msdb].[dbo].[sysmail_allitems] mail

inner join [msdb].[dbo].[sysmail_event_log] err

ON err.mailitem_id = mail.mailitem_id

WHERE mail.send_request_date <> 'sent'

order by mailitem_id desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

happy55 Ten Centuries Points: 1243 More actions · Answer 5

Thanks for your help. It says mail id queued but no email is send. Below is the message in mail logs

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2015-06-22T05:30:17). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 4.3.2 try again later).

)

The problem is occurring only when I say @attach_query_result_as_file = 1

It else without attachment it is executing fine always.

Lowell SSC Guru Points: 323518 More actions · Answer 6

Mailbox unavailable implies the wring target email ;you are sending to a full email address, right?

you must be sending to "happy55@somedomain.com", and not hoping exchange will convert "happy55" tot eh right email address, right?

tartgets for sp_send_dbmail must be an actual email address, and not a distribution list or alias that might be seen in Exchange.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

happy55 Ten Centuries Points: 1243 More actions · Answer 7

Thanks.I am sending to actual email address .Had it been problems with email address it would not deliver without attachments. its only an issue when I send email with attachment.

Lowell SSC Guru Points: 323518 More actions · Answer 8

hmm; how big is the attachment, then? what is the attachment extension name? or at least how many rows in the query?

specific mail servers often have rules on attachment size, 2 meg, 10 meg, 20 meg, etc. they also might have rules like no xls due to macros, but csv is OK.

if you attach something that ends up geing greater than the limit, i'd expect the mailserver to throw a more specific error about the mail size thought.

if you send the same attachment to a yahoo/gmail account or other email, does it go through to the other mail servers?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

happy55 Ten Centuries Points: 1243 More actions · Answer 9

The query is returning just one row and it is name as test.txt.

I tried sending to Gmail,it did not deliver and same error in logs.This is making me crazy .

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2015-06-22T10:20:57). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 4.3.2 try again later).

)