SQL 2000 and Setting up SQL Mail

  • Wesley Brown

    SSChampion

    Points: 12045

    I am at my wits end with this. I know mail setup is easy. I setup the mail profiles and operators the test run everytime but when a scheduled job or backup is suppost to email me it won't. I get mapi errors and nothing comes down to me. Anyone else having mail problems?

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Andy Warren

    SSC Guru

    Points: 119686

    What about if you try to send using xp_sendmail, does that work?

  • Wesley Brown

    SSChampion

    Points: 12045

    Yep... Its a known bug with sql 2000 I applied sp1 to my dev box and all is well now. It wasn't sending reports from backups but not failing the final step ether. I did some digging about a month ago on it and didn't see anything about it. Oh well.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Andy Warren

    SSC Guru

    Points: 119686

    I will have to look at the fixlist - kinda big to remember it all! I haven't had much problem with email.

    Andy

  • Wesley Brown

    SSChampion

    Points: 12045

    It is friggin HUGE. its like 300 fixes rolled into sp1. I did fix alot of little things I was worried about in our test lab.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719108

    No problems here. I get notifications from job failures as well as xp_sendmails that I schedule.

    What is your mail setup? Do you have Exchange (version?), Outlook (version). Is this in a domain, what accounts are SQLAgent and SQL Server running under (local, rights, etc.).

    Steve Jones

    steve@dkranch.net

  • Wesley Brown

    SSChampion

    Points: 12045

    All my mail problems went away after applying sp1. we run exchange2000 fully patched the mapi client is outlook2000 sp1 and the account it a domain account with full mail rights. Scheduled jobs send mail but backup reports never made it and never generated an error. One problem I do have is when I try to use the contacts list instead of a regular email address i get a mapi resolution error from sqlserveragent. i.e. in outlook you can just use a name like john doe that doesn't resolve but john.doe@mynetwork.com does. I haven't setup reporting to groups yet ether so I don't know if it will send to outlook list yet or not.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Wesley Brown

    SSChampion

    Points: 12045

    Ok here we go again. After applying sp1 to the test server it sends mail fine. I applied sp1 to three servers last night all of them configured the same and they still only send mail from xp_sendmail,job alerts but not from backup notifications. The Q article said that this was fix in sp1 and did seem to fix it in test but not production. Any other ideas what could be wrong? I've got a mapi client outlook2ksp1 on every box they all use the same domain account to start sqlserver/sqlagent and use the mail profile in outlook to send mail. When I do go to operator -> properties and select a name from the contacts list I get a mapi error of -2147219712 I can't find this error anywhere on MS's site. I don't know what else to do but maybe put a call into ms support at this point. I've run sqldiag and compared the dumps to the only machine that does run against the others that don't and they all look identical.

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719108

    If you can, log into the production boxes iwth the SQL Agent account and verify you can send and recieve mail. something may have been lost.

    Then log out and restart the SQL Services (or reboot) and then try it again.

    BTW: Is the SQL Server and SQL Agent account the same?

    Steve Jones

    steve@dkranch.net

  • Wesley Brown

    SSChampion

    Points: 12045

    Ok, I can send and recive mail from the outlook client. I stopped and restarted the services I attempted to send mail via xp_sendmail and get this

    Server: Msg 18025, Level 16, State 1, Line 0

    xp_sendmail: failed with mail error 0x80070005

    I'm at win2k sp2 sql2k sp1 outlook2ksp1. I'm rebuilding the test box now to see if there is something diffrent with the test and production configs. I don't think so though.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Wesley Brown

    SSChampion

    Points: 12045

    Ok. I reinstalled the test sql box. Setup outlook2k using a agent domain login it is a domain admin it has a mailbox I can send and recive mail with outlook I created a default mail profile copied it to sqlagent and deleted it. I can't send mail via xp_sendmail nor does the backup reporting send me a report BUT the job secheduler does send me email:

    JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' was run on 7/12/2001 at 2:02:00 PM

    DURATION: 0 hours, 0 minutes, 3 seconds

    STATUS: Succeeded

    MESSAGES: The job succeeded. The Job was invoked by Schedule 1 (Schedule 1). The last step to run was step 1 (Step 1).

    This tells me that mail is running and some part of sql can talk to it like the sqlserveragent. I just don't know what else to look at. this is a clean install all the way around.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • K. Brian Kelley

    SSC Guru

    Points: 114485

    Exchange Server 2000 say you? Here's a Q article on that there error:

    http://support.microsoft.com/support/kb/articles/Q293/4/22.ASP

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Wesley Brown

    SSChampion

    Points: 12045

    Did that.... Did it last time around and again after the sp1 patch. Any other ideas?

    🙂

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • K. Brian Kelley

    SSC Guru

    Points: 114485

    Swift kick to the server (if reachable) with a steel-toed boot? Hmmm, I'll keep digging.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Wesley Brown

    SSChampion

    Points: 12045

    lol. If I could get away with it I would but I sit right next to our Sr. admin he'd kill me.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

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

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