Post reply

SQL Job Reports Emails Failure but Emails are Actually Sending

  • May 10, 2016 at 8:58 am

    #307373

    We have a SQL Transact-SQL Script (T-SQL) Job that sends 100+ emails to 100+ different users in SQL 2012. According to the job history the step is failing and the emails are not sending, however it turns out the emails are actually being delivered successfully.

    The failure log shows the messages queued and then at the very end Step Failed. Everything looks ok?

    SQLSTATE 01000] (Message 0) Mail (Id: 22723) queued. [SQLSTATE... The step failed.

    Can anyone please advise why the the job might be reporting a failure?

    thanks

  • May 10, 2016 at 11:15 am

    #1877903

    can you show the entire error message? maybe show the entire contents of the step that is cursoring through the emails;

    it looks like the email sent correctly, but an error message you cut off clearly is showing the next error....maybe an update that is inside your cursor that is sending email is failing to say "sent" or something...so you think the problem is mail, but it's something deeper in the job step.

    it could even be a developers debug item raising error 0 (RAISERROR) so they could visualize progress.

    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!

  • May 11, 2016 at 6:50 am

    #1878144

    I advise going to the Advance tab of the job step and establishing a text log file. Then click the Include Step Output box. This will log everything the step does to the text file and won't cut anything off (like SQL Agent does). After the job fails, open the text file and search for error: (including the colon) to find your error messages.

    This has saved my bacon a number of times when job history GUI window just doesn't have the space for what you need to see.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • May 11, 2016 at 9:28 am

    #1878287

    Thanks, i'll give this a go

  • May 11, 2016 at 9:39 am

    #1878292

    The Email did send correctly despite the error, the owner has since tried amending the job and running the job but for just 2 sites and the emails were recieved and job ran successully with no errors so it sounds like SQL can't cope with the number of emails which is about 160, all with a PDF attachments.

    This is the error

    Date09/05/2016 14:13:51

    LogJob History (Send RM PL Site Summary Sheets)

    Step ID2

    ServerServer\JIGSAW

    Job NameSend RM PL Site Summary Sheets

    Step NameEmail Sites own Individual P&L Summary Files

    Duration00:00:04

    Sql Severity16

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: CARAVAN-CLUB\Jigsaw. ... (Id: 22652) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22653) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22654) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22655) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22656) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22657) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22658) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22659) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22660) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22661) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22662) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22663) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22664) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22665) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22666) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22667) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22668) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22669) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22670) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22671) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22672) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22673) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22674) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22675) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22676) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22677) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22678) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22679) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22680) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22681) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22682) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22683) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22684) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22685) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22686) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22687) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22688) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22689) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22690) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22691) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22692) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22693) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22694) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22695) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22696) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22697) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22698) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22699) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22700) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22701) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22702) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22703) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22704) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22705) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22706) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22707) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22708) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22709) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22710) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22711) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22712) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22713) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22714) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22715) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22716) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22717) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22718) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22719) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22720) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22721) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22722) queued. [SQLSTATE 01000] (Message 0) Mail (Id: 22723) queued. [SQLSTATE... The step failed.

    Thanks for your help

  • May 11, 2016 at 11:00 am

    #1878322

    That still looks just like the Agent error (what you see when you open up the job history). Not what's written into a text file.

    Try adding the text file as I suggested. It should give you actual error text.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 6 posts - 1 through 6 (of 6 total)

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