Database Mail

  • I am new to SQL 2005 and am not a DBA. I have questions for setting up Database Mail. I have spent 2 days searching, which has left me with even more questions. The send test email works but job status notifications aren't sent. To start with:

    1. I am using 64-bit SQL SP3 and MS Technet KB908360 says 64-bit is not integrated with mail and has to be set up manually. The article is dated 2007 and mentions SP1 so I am wondering if it is still apllicable.

    2. I see Database Mail can be configured per instance but is best practice to use a separate SMTP account per instance?

  • kfewer (12/22/2009)


    I am new to SQL 2005 and am not a DBA. I have questions for setting up Database Mail. I have spent 2 days searching, which has left me with even more questions. The send test email works but job status notifications aren't sent. To start with:

    1. I am using 64-bit SQL SP3 and MS Technet KB908360 says 64-bit is not integrated with mail and has to be set up manually. The article is dated 2007 and mentions SP1 so I am wondering if it is still apllicable.

    2. I see Database Mail can be configured per instance but is best practice to use a separate SMTP account per instance?

    1. I would follow the suggestions in the article and setup the mapi.

    2. 1 SMTP should do.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 1. Database Mail IS supported on 64-bit. This article states that it is "fully-supported on 64-bit".

    2. It depends on if you want to be able to identify which instance the mail comes from based on account.

  • Have you gone through the steps of setting up an operator under SQL Agent?

    After setting up database mail, you have to go under operators in SQL agent and setup an operator. After you setup an operator you have to restart the SQL Agent.

    Now, when you go to Notifications on the job properties and check E-Mail your operator should be listed in the drop down box.

  • Thanks for your reply, Jack.

    1. The KB article does say the issue with 64-bit was addressed in SP1, so it shouldn't be an issue. We do have another 64-bit server where the mail is working. I have been comparing the servers and two differences stand out:

    a) The working server has only one instance. The app is using the default instance.

    b) The working server's Agent is using the local system account and the non-working server's agent is using a domain service account. I am not sure if/how this affects the notification.

    This is the error in the job history: "Could not obtain information about Windows NT group/user 'sa', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    I made some changes yesterday and I did get an email to tell me the email notification failed. Hm. Also, the 'Send Test Email' works.

    2. We will have multiple instances on this server so I would prefer to identify the instance. However, if best practice is to only use one SMTP account for all instances, I could identify the instance within the message. One SMTP account for all instances would mean less overhead. What are the disadvantages for this?

  • who owns the job?

  • SQL sa account for the instance

  • I have had similar problems when database mail profile was not set to public. Is the public checkbox checked for your profile?

  • Yes. It is set to public. I am also getting an error on all maintenance jobs: "Login failed for user 'sa'.", even though all the steps except email appear to be working.

    I recently removed and reinstalled SQL after a hardware change. After the vendor restored the database, I was unable to login to the instance (Management Studio) using the sa account. Although they fixed this, the maintenance jobs (which were created after the re-install) all get sa login errors. I am thinking it may be related. Unfortunately the vendor is closed for the holidays.

  • Is the server set for Mixed Authentication or Windows only?

  • It is set for Mixed. Although the owner of the maintenance jobs are all sa, the Activity monitor lists the user of msdb as the domain service account and all other databases has sa as the user. Not sure if this is of any significance.

  • I switched the agent to local system account (restarted it) and ran the job but it still failed.

  • I have also set email notification on the job itself and I do receive emails for that. It is the email notification task within a maintenance plan that fails.

  • Finally success! It turned out to be a missed configuration step: Setting the public profile as "Default". Thanks everyone for your help!

  • Good Work! Sorry to not be of more help.

    Thanks for posting your solution

Viewing 15 posts - 1 through 15 (of 16 total)

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