SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mail failing with attachment


Database Mail failing with attachment

Author
Message
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
Tried both suggestions, first @append_query_error = 1, same result. Then changed file name to @query_attachment_filename = NULL and got the error message added twice to sysmail_log after executing the job. I have to say at this point that this is weird.

And yes you have the scenario correct, except what do you mean by "The sp_send_dbmail returns successfully"? No retries in code. Just those attempted by db mail.

Thanks very much.
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
and BTW - when the sproc is executed from management studio & not called as sql server agent job, it works. Weird.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23558 Visits: 5314
bdanks 85818 (6/20/2013)
And yes you have the scenario correct, except what do you mean by "The sp_send_dbmail returns successfully"? No retries in code. Just those attempted by db mail.

I mean that the call to the sproc returns without error and the retries I meant are configured at the database mail level, retries, max attachment size and such are set at that same spot..

CEWII
SQL Show
SQL Show
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 1078
Hi,
could it be a access issue? Sql service and agent are running under different accounts?
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
the sproc executes fine & sends the email with the attachment when called from management studio so I would say the sproc and the call to sp_send_dbmail within it are fine. When executing the job from sql server agent, the error mentioned in original email occurs.

Increased retry attempts to 10. No change.
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
Maybe - when email is sent successfully from management studio the last_mod_user entry in sysmail_log is NT AUTHORITY\NETWORK SERVICE and when it fails after invoking sql agent job that entry is sa.
SQL Show
SQL Show
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 1078
Hi,
could it be a access issue? Sql service and agent are running under different accounts?
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
This post was a reply to your post:

Maybe - when email is sent successfully from management studio the last_mod_user entry in sysmail_log is NT AUTHORITY\NETWORK SERVICE and when it fails after invoking sql agent job that entry is sa.

We are a small company, no dba. Not sure how to resolve account issue if that is it.

Thanks.
SQL Show
SQL Show
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 1078
I suggest to change 'sa' account to the account what you are running in ssms in sql job. Let us see it solves the issue.
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 56
Changed both server and agent to run as Network Service. Same results. Changed both server and agent to run under same network account name & password. Same results.

Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search