Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Failed to Notify Operator via Email

    I'm trying to get a SQL server job to send me an email when the job completes.
    1. Database mail is enabled in the SAC
    2. SQL Server Agent's alert system is configured to use DBMail
    3. Database mail is enabled, and "send test email" succeeds (to the same email I want to send to).
    4. When the job completes, I don't see any new entries in msdb.sysmail_allitems
    5. Looking at the job history, entries show:
      The job succeeded. The Job was invoked by User DOMAIN\GeorgeV. The last step to run was step 1 (test). NOTE: Failed to notify 'GeorgeV' via email.
    6. The operator I set up has the correct email address, is enabled, doesn't have the "on duty schedule" checked and in the history of this operator it says "never emailed".
    7. both sp_notify_operator and sp_send_dbmail executes correctly.
    8. Database Mail log looks a little funny:
      Code:
      17/10/2008 09:24:04 DatabaseMail process is shutting down
      17/10/2008 08:56:11 DatabaseMail process is started
      17/10/2008 08:28:38 DatabaseMail process is shutting down
      17/10/2008 08:12:07 DatabaseMail process is started
      furthe back in the log dbmail starts around 6am and stops 10 minutes later. I doubt this is normal behavior?

    @@Version returns:

    Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    I'm all out of ideas, can anyone suggest what I can try to get this issue resolved?
    If any further information is required to solve this, let me know and I'll post it. Cheers!
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George - you posted recently about getting database mail set-up. I assume you solved that problem right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Frick, I did as well! Sorry, plain forgot.

    http://www.dbforums.com/showthread.php?t=1634041

    Yes, it is a follow on from the same issue.
    It took them about 4 weeks, but they ended up restarting SQL Agent, and reconfiguring DBMail from the ground up, still the same result.

    Sorry for double posting
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have you tried invoking sp_send_dbmail directly from T-SQL?

    EDIT - oh yes - I'm losing my edge:
    TSK!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Your version looks a tad out of date. Any prospects of getting SP2 on there? Or even SP1?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, I know it's RTM... Is this a known bug in <SP?
    This has been suggested, but I'd still rather know what's causing the problem anyway; who knows whether it will still occur post update?
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can't see it listed as a fix, although there are some changes to the dbmail stored procedure in sp2: http://support.microsoft.com/kb/921896/
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This smells of deja-vu all over again. I think we've had this discussion.

    -PatP
    Attached Thumbnails Attached Thumbnails 200810201344.GIF  

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That part is correctly set up already

    Cheers though
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    just for yuks and grins, what do you have in this screen:
    Attached Thumbnails Attached Thumbnails Notifications.jpg  

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh yee of little faith

    Email <operator name> When the job completes.
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    And did you restart SQL agent after you made the changes Pat asked you to confirm?

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    After each change I restart SQL Agent and test again... not having any luck with this issue!
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, let's see if the linkage of everything in the tables seems correct. You should get values for each column. If you don't, then Pootle wins. Also, verify your own email address, and that the sending address (while not necessarily a mailbox) is a valid email address syntactically.
    Code:
    set nocount on
    
    declare @instname nvarchar(100)
    declare @instnum nvarchar(20)
    declare @registrypath nvarchar(1000)
    set @instname = isnull(convert(nvarchar(100), serverproperty('InstanceName')), N'MSSQLSERVER')
    
    create table #registry
    (KeyName nvarchar(100),
     value nvarchar(1000))
    
    insert into #registry
    EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instname
    
    select @instnum = value from #registry
    set @registrypath = N'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instnum + '\SQLServerAgent'
    
    delete #registry
    
    insert into #registry
    exec xp_regread N'HKEY_LOCAL_MACHINE', @registrypath, N'DatabaseMailProfile'
    
    select j.notify_level_email, o.name as "Operator Name", o.email_address, p.name as "Profile Name", a.name as "Account Name", a.email_address
    from sysjobs j join
    	sysoperators o on j.notify_email_operator_id = o.id cross join 
    	(sysmail_profile p join
    	sysmail_profileaccount pa on p.profile_id = pa.profile_id join
    	sysmail_account a on pa.account_id = a.account_id join #registry r on p.name = r.Value)
    where j.name = Your Job name here
    
    drop table #registry

Posting Permissions

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