SQL Server Performance

SQL Mail

Discussion in 'General DBA Questions' started by CanadaDBA, Oct 21, 2004.

  1. CanadaDBA New Member

    I want to make SQL-Server send me email. I have installed Microsoft Outlook 2000. I have created Mail profil. I have set SQL Mail and SQL Server Agent mail. MS Outlook sends email successfully.

    The problem is that SQL Server and MS Outlook have problem using a .pst file. Whoever uses the file first, the other one generates an error which says the file is in use by another application.

    Even when I test sending email or paging an operator (when SQL server has been run first), then SQL Server sends the email but I don't recieve until I stop SQL server, then run the MS Outlook on the server. A few minutes later I have the emails in my mail box and a message on my cell.

    What's wrong? What should I do?

    CanadaDBA
  2. Twan New Member

    Shooting from the hip, I'd say that you have to use Exchange, and not a pst file...?

    SQLMail can be problematic, which is why a lot of people have written their own stored proc to send emails via SMTP rather than MAPI, there are various examples on this site.

    Cheers
    Twan
  3. derrickleggett New Member

    1. Make sure you have the latest server pack installed for SQL and Outlook.
    2. Get process explorer from www.sysinternals.com and figure out what else is using it if that doesn't work.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. CanadaDBA New Member

    "Shooting from the hip, I'd say that you have to use Exchange, and not a pst file...?"
    I am not sure we have Exchange or budget to buy it. How can I get ride of the pst file? Any link or suggestion?

    "SQLMail can be problematic, which is why a lot of people have written their own stored proc to send emails via SMTP rather than MAPI, there are various examples on this site."
    Does this mean I don't need to have Microsoft Outlook running on my server? I am going to search the site. Would you send me some links as well?

    Thanks,


    CanadaDBA
  5. satya Moderator

    http://www.sqlteam.com/item.asp?ItemID=5003<br /<a target="_blank" href=http://www.sqlteam.com/Item.asp?ItemID=8720>http://www.sqlteam.com/Item.asp?ItemID=8720</a><br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech>http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech</a><br /<a target="_blank" href=http://www.devasp.net/net/search/display/category705.html>http://www.devasp.net/net/search/display/category705.html</a><br /<a target="_blank" href=http://www.devasp.net/net/search/res/r3003.html>http://www.devasp.net/net/search/res/r3003.html</a><br /><br />... talking about same point [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  6. Twan New Member

    HI ya,<br /><br />thanks Satya, I knew you'd come to the rescue with the links <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />getting rid of the pst file with outlook but without Exchange, I'm not sure if that is feasible? If you have outlook but no exchange then presumably Outlook itself will be sending/receiving the emails, hence the problems you're seeing? only one process at a time can have the pst file open...<br /><br />Cheers<br />Twan
  7. CanadaDBA New Member

    Then I have to leave the MS Outlook running on the server and that's why SQL-Server Agent mail test fails and says the .pst file is not accessible. <br />But same situation is set up by previous DBA on our production and it is working. MS Outlook is running on the box and SQL Mail and SQL Agent send emails. What's wrong with my setting on new server? <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br />HI ya,<br /><br />thanks Satya, I knew you'd come to the rescue with the links <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />getting rid of the pst file with outlook but without Exchange, I'm not sure if that is feasible? If you have outlook but no exchange then presumably Outlook itself will be sending/receiving the emails, hence the problems you're seeing? only one process at a time can have the pst file open...<br /><br />Cheers<br />Twan<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />CanadaDBA
  8. CanadaDBA New Member

    Thanks Satya,<br />I was waiting for you with some links. But you provide more than some [8D]<br />And I have to do a lot of study [B)]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br /<a target="_blank" href=http://www.sqlteam.com/item.asp?ItemID=5003>http://www.sqlteam.com/item.asp?ItemID=5003</a><br /<a target="_blank" href=http://www.sqlteam.com/Item.asp?ItemID=8720>http://www.sqlteam.com/Item.asp?ItemID=8720</a><br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech>http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech</a><br /<a target="_blank" href=http://www.devasp.net/net/search/display/category705.html>http://www.devasp.net/net/search/display/category705.html</a><br /<a target="_blank" href=http://www.devasp.net/net/search/res/r3003.html>http://www.devasp.net/net/search/res/r3003.html</a><br /><br />... talking about same point [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />CanadaDBA
  9. satya Moderator

    Thanks Twan when they are required it will be handy.

    One for the KBA FAQ refers:
    Setting up the Internet Mail service creates a Personal Store file (.pst) to store downloaded mail messages from the POP3 server. This .pst file is used by any service that is using this mail profile, and causes a conflict if SQL Mail and SQL Agent Mail both use the same mail profile. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    http://support.microsoft.com/kb/313969/EN-US/

    If you specify a location for the .pst file, do not use a network share.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    http://support.microsoft.com/kb/297019/EN-US/

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. CanadaDBA New Member

    I forgot to ask you about my question Twan? Would you reply that?

    quote:Originally posted by FarhadR

    "SQLMail can be problematic, which is why a lot of people have written their own stored proc to send emails via SMTP rather than MAPI, there are various examples on this site."
    Does this mean I don't need to have Microsoft Outlook running on my server? I am going to search the site. Would you send me some links as well?

    CanadaDBA

    CanadaDBA
  11. satya Moderator

    KBAhttp://support.microsoft.com/kb/281293/EN-US/ defines
    Due to the limitation of only providing extended MAPI support, SQL Server 2000 requires a Microsoft Outlook 2000 client (or later version).


    Refer to thehttp://www.sqlteam.com/item.asp?ItemID=5003 link above talking about using SMTP with SPs.

    HTH


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. Twan New Member

    HI ya,

    if you go down the smtp route, then you won't need outlook installed or running on the server, you will only need the ability to talk to an smtp server (this can very easily be installed on a server, ideally one that is running iis already

    Cheers
    Twan
  13. CanadaDBA New Member

    This is an intresting part that I would like to implement on my server. Would you please explain how can I install it?
    Thanks,

    quote:Originally posted by Twan

    HI ya,

    if you go down the smtp route, then you won't need outlook installed or running on the server, you will only need the ability to talk to an smtp server (this can very easily be installed on a server, ideally one that is running iis already

    Cheers
    Twan

    CanadaDBA
  14. Twan New Member

  15. CanadaDBA New Member

    My problem is solved. I ran the MS-Outlook on the server box itself and then locked the computer. Now, the SQLAgent's test sends a test to my email.Also successful jobs send me an email.

    Before this, I was working through Terminal Services and had mentioned problems. Although MS-Outlook was not running on the server box, but when I was going to run in it through Terminal Services, I recieved and error indicating the pst file is in use by another application while when I ran the MS-Outlook in the server box itself, I didn't get any error and it is working now.

    * * *

    Now, I am going to work on sending email wintout Microsoft Outlook. I am going to use what Twan and Satya have posted. Thanks!


    CanadaDBA
  16. simas New Member

  17. Argyle New Member

    It doesn't sound good if you need to lock the server to be able to get it to work. If this server is rebooted and noone logs in again it might not work.

    Why do you need to start Outlook on the server? Once Outlook has been installed and Agent/SQLMail is configured it will work without having to start Outlook.
  18. CanadaDBA New Member

    How is it possible? It seems Outlook must be running. Outlook is not a service so, I have to login on the server. Can you explain more? Have you done it before?

    quote:Originally posted by Argyle

    It doesn't sound good if you need to lock the server to be able to get it to work. If this server is rebooted and noone logs in again it might not work.

    Why do you need to start Outlook on the server? Once Outlook has been installed and Agent/SQLMail is configured it will work without having to start Outlook.

    p.s. I have Microsoft Outlook 2000.

    CanadaDBA
  19. Argyle New Member

    You only need to start Outlook during the install. During install you login as the sql service account and create a mailbox (if you use Exchange server) or a profile (with no Exchange server) for this users. But after that the sql service account can use this mailbox without having Outlook client running on the actual server.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
    http://www.sqljunkies.com/Article/0A1F961B-8E50-4E76-8FCE-31A0F800FCC7.scuk

    Connecting to an Exchange server is to prefer since you don't need any .pst file and you've seen what issues can come from that.

    You could get advanced and replace the builtin functions for SQLMail and SQLAgent to ones that use SMTP instead without the need for Outlook:
    http://home.comcast.net/~b.wunder/15332.htm
  20. CanadaDBA New Member

    I am badly confused!!
    Your both links are referring to a mail client and suggest Outlook for that reason. So, I have to have the Outlook running on my server to be able to send emails. I have tried to do it without a mail client but couldn't find how.[B)]

    I foundhttp://www.aspemail.com which is a service and doesn't need to log on the server to work. It is free, too. What's your idea about that?

    quote:Originally posted by Argyle

    You only need to start Outlook during the install. During install you login as the sql service account and create a mailbox (if you use Exchange server) or a profile (with no Exchange server) for this users. But after that the sql service account can use this mailbox without having Outlook client running on the actual server.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
    http://www.sqljunkies.com/Article/0A1F961B-8E50-4E76-8FCE-31A0F800FCC7.scuk

    Connecting to an Exchange server is to prefer since you don't need any .pst file and you've seen what issues can come from that.

    You could get advanced and replace the builtin functions for SQLMail and SQLAgent to ones that use SMTP instead without the need for Outlook:
    http://home.comcast.net/~b.wunder/15332.htm

    CanadaDBA
  21. fhanlon New Member

    Personally I went the SMTP route. I didn't install OUTLOOK or Exchange. I just call a stored proc that references the CDOSYS objects. All you need is an SMTP server somewhere on the network. TWAN's link is great, but there are lots of others as well. I found writing (or adapting and existing) proc is much easier than setting up SQL mail.
  22. Argyle New Member

    I didn't say without a mail client. What I mean with "Outlook running" is that you don't need to log in on the server and start the outlook client and then have it open in a window. You just need to have the outlook client installed.
  23. djradon New Member

    I've encountered the exact same problem as CanadaDBA: Using SQL Server and Outlook-provided MAPI, SQL Agent mail gets queued "somewhere" and isn't sent until the SQL Agent is stopped (unlocking the .pst mailbox) and Outlook is started.
  24. djradon New Member

    May have answered my own question:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;311231

    "Security features built into Outlook 2002 and later versions of Outlook cause SQL Mail to stop responding with SQL Server 6.5 and SQL Server 7.0. For Outlook 2002 or later versions to work with SQL Mail and SQL Server 2000, you should have an Exchange server. We do not recommend that you use Outlook 2002 or later versions with SQL Mail and Simple Mail Transfer Protocol (SMTP) or Post Office Protocol 3 (POP3) Internet mail servers."
  25. djradon New Member

    Yes, indeed, I uninstalled Outlook 2002, installed Outlook 2000, configure MAPI to use my SMTP, and at first it didn't work. Then I logged off the account, and all the test messages came through. That's an interesting point: make sure to log off the SQL Mail user before testing the SQL mail.

Share This Page