Results 1 to 14 of 14

Thread: Database Mail

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Database Mail

    We have a job scheduled to notify an operator on failure. However, the following is written to the error logs each time this is attempted:
    [264] An Attempt was made to send an email when no email session has been established

    To troubleshoot I have re-created the issue by setting up a test job that executes a T-SQL statement, notifying the following operator when the job succeeds (i.e. regardless of outcome).
    Code:
    USE [msdb]
    
    EXEC msdb.dbo.sp_add_operator
         @name = N'georgev'
       , @enabled = 1
       , @email_address = N'<email address here>'
    I have tested the Database Mail setup by firing off test emails from all profiles and this has succeeded everytime.

    I am also able to execute sp_send_dbmail without problem from QA
    Code:
    EXEC msdb..sp_send_dbmail
       @profile_name = '<profile name>'
     , @recipients   = N'<email address here>'
     , @subject      = N'test'
     , @body         = N'test'
     , @body_format  = 'HTML';
    As far as I have disproved this being a permissions issue as I have scheduled the job to run under various different security contexts and have had the same result each time.

    I'm banging my head against a brick wall at this point now - what should I be looking for to try and solve this issue? What am I missing!?

    Cheers,
    George
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya googled before headbutting the wall right?
    http://forums.microsoft.com/MSDN/Sho...22173&SiteID=1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ugh, no internet until now and I'm far too gone to follow that thread until tomorrow.
    Let's hope the interwebz is working then
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Restarting SQL Agent was my initial suggestion to the Infra boys, but they rebutted the idea
    Cheers for the extra ammunition
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The Infra Boyz rejected a "stop it and start it again" procedure????//??/ WTF???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Heck, I was as shocked as you.
    I think it was more to do with the fact that I suggested it before they did
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I recently built an x64 cluster and applied sp2 "out of the box" before I customized anything.

    This client prefers using a standardized SQL script to enable mail, and I used that script. As I was setting things up, one thing that I always check is that after setting up SQL Mail that the SQL Agent properties show mail enabled, and this time it was not enabled!

    The bad news is that I still don't have mail working on this cluster, and can't figure out why... I'm sill looking for the reason that mail isn't working (and watching the SQL and event logs like a hawk), but I'm seriously thinking of asking for down time and seting up the mail configuration again manually to see if that will correct the problem.

    George: If you have set database mail up manually, take a minnute to check the SQL Agent properties. I think that is the most likely culprit under those conditions. If you do find an answer, PLEASE let me know because it might help me solve my problem (at least the one with database mail anyway)!

    -PatP

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey Pat,

    As my understanding of the history goes, database mail has worked in the past and only recently has it had problems - so I don't think our issues are related... Also, this is not a 64-bit install, and on my Google travels I noticed some people had issues with 64 and not x86

    Just out of interest Pat - can you execute sp_send_dbmail? Does anything come up in the logs at all - start/stop activity but no mail able to send?

    Sadly I didn't set up this server (I never get to do any of that stuff really!) which is partly why it's never been my problem

    If I come across anything which might be of use to you, I'll let you know.

    Cheers
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Interestingly enough, any user-initiated email works on these servers, but service initiated emails fail. I can call any mail related stored procedure and they work just fine. It is just services like SQL Agent that are giving me problems.

    -PatP

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's the issue we're experiencing too, which is why my first leap of logic was permissions... But that didn't come up fruitful.

    I'm catching up with one of the Infra boys next week and will get him to restart SQL Agent.

    Is there any activity in your logs about Databse Mail sessions starting or stopping?
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Funnily enough I'm now struggling to get mine working on my dev machine. Not trying that hard though TBH. Perhaps some engineer left a line of code something like:
    Code:
    IF today() < 20080915....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes! I remember coding that line.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why-I-oughtta!



    obligatory winky ;-)
    George
    Home | Blog

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if (user=='georgev') Environment.Exit()

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •