Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database Mail failing with attachment Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 9:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1466023
Posted Thursday, June 20, 2013 10:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
and BTW - when the sproc is executed from management studio & not called as sql server agent job, it works. Weird.
Post #1466026
Posted Thursday, June 20, 2013 10:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1466030
Posted Thursday, June 20, 2013 10:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Hi,
could it be a access issue? Sql service and agent are running under different accounts?
Post #1466032
Posted Thursday, June 20, 2013 10:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1466033
Posted Thursday, June 20, 2013 10:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1466035
Posted Thursday, June 20, 2013 11:04 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Hi,
could it be a access issue? Sql service and agent are running under different accounts?

Post #1466040
Posted Thursday, June 20, 2013 11:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.

Post #1466043
Posted Thursday, June 20, 2013 11:24 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1466044
Posted Friday, June 21, 2013 10:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1466293
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse