Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    17

    Unanswered: Trouble with SQL Mail notifications

    I am trying to set up event notifications via email (MS Exchange). Following the instructions here...
    http://msdn2.microsoft.com/en-us/library/ms190724.aspx

    I have an outlook profile created on my sql server for the same domain account which starts up SQLSERVERAGENT. I can send/recieve emails in this account, and running xp_sendmail in Query Analyzer works as expected.

    However, when trying to set up an "operator" and testing the email ability, I get the error...
    Error 22022: SQLServerAgent Error: The SQLServerAgent mail session is not running; check the mail profile and/or the SQLServerAgent service startup account in the SQLServerAgent Properties dialog.

    I am running Outlook 2003 using Exchange. I seem to be having the same problem as this person.
    http://www.experts-exchange.com/Data..._21627245.html

    However, it makes reference to MSExchangeIS which I cannot find on my SQL Server. I would assume this to be a service of Exchange which would be on a seperate server entirely. If this is just some type of "connector" service that can be installed on any server, then how would I go about installing it? I presume that I need this service to get these mail notifications working, no?

    This must be a very common situation since everyone needs to know when their jobs are working or not. Any help is greatly appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    It sounds to me like you just need to correctly set the Mail Profile in the SQL Agent properties. The Mail profiles for SQL Agent and SQL Server are separate (and can actually be different, if you so choose).

    Verify that you have the Mail profile set correctly in the SQL Agent properties and retry your operation.

    Not that I'm trying to upsell you on anything, but Database Mail in SQL 2005 is MUCH better!

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2006
    Posts
    17
    I'm quite certain that I have the Mail Profile set up correctly. If I didn't, then how would the xp_sendmail work?

    Here is how I have the mail set up...
    http://img.photobucket.com/albums/v1...-/sqlmail2.jpg
    http://img.photobucket.com/albums/v1...-/sqlmail3.jpg

    The key thing that I am looking for is information about the MSExchangeIS service. Obviously it is a part of exchange, but our exchange server is not on the same machine as the SQL server. Do I need to have Exchange installed on my SQL server in order to have this work? I sure hope there is an alternative.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by lotrtrotk
    I'm quite certain that I have the Mail Profile set up correctly. If I didn't, then how would the xp_sendmail work?
    SQL Mail is completely separate from SQL Agent Mail. xp_sendmail is related specifically (and only) to SQL Mail. It has nothing to do with SQL Agent Mail. The attached images confirm for me that you are configuring SQL Mail and not SQL Agent Mail. The fact that it (xp_sendmail) works is good, since it means that you have done the bulk of the hard work.

    The property you want to configure is accessed by right-clicking on the "SQL Server Agent" node (under Management) and selecting Properties. The field you want to configure is in the middle of the screen (labeled Mail Profile).

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Sep 2006
    Posts
    17
    Well how about that...You're exactly right.
    I thought that SQL Mail & SQL Agent Mail were the same thing. Thank you for clearing that up for me.

    One more quick question. SQLSERVERAGENT, from what I understand, is ONLY responsible for the jobs and maintenance tasks. So can I safely assume that if no jobs are currently running, it is ok to restart this service without affecting the end user?

    If not then I will have to schedule some down-time to restart this service.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Just thought I'd mention that if you are on 2005, you should be using Database Mail, not SQLMail. SQLMail has been deprecated.

    Database Mail is also much easier to set up, from what I hear.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by jezemine
    Just thought I'd mention that if you are on 2005, you should be using Database Mail, not SQLMail. SQLMail has been deprecated.

    Database Mail is also much easier to set up, from what I hear.
    Quote Originally Posted by hmscott
    Not that I'm trying to upsell you on anything, but Database Mail in SQL 2005 is MUCH better!
    Yep! I already tried upselling that one. And yes, it is MUCH easier to configure and much more reliable as well. I HATE SQL Agent Mail.

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by lotrtrotk
    One more quick question. SQLSERVERAGENT, from what I understand, is ONLY responsible for the jobs and maintenance tasks. So can I safely assume that if no jobs are currently running, it is ok to restart this service without affecting the end user?

    If not then I will have to schedule some down-time to restart this service.
    You are correct. Stopping the service will cancel any job that is currently running. Stopping and restarting the service when no jobs are executing should not impact the system at all.

    One side note, even when executed as a scheduled task, backups are always executed in the context of the SERVER service (not the agent service). I always set up both the SERVER service and the SQLAGENT service to run under the same domain account. It just makes life easier.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Sep 2006
    Posts
    17
    Quote:
    Originally Posted by jezemine
    Just thought I'd mention that if you are on 2005, you should be using Database Mail, not SQLMail. SQLMail has been deprecated.

    Database Mail is also much easier to set up, from what I hear.

    Quote:
    Originally Posted by hmscott
    Not that I'm trying to upsell you on anything, but Database Mail in SQL 2005 is MUCH better!
    LOL, great! So do you guys got any other sales-pitches to offer the executives so they'll approve such an upgrade?

    I've wanted to get up to SQL2005 for quite a while, but there are just too many variables right now to do it safely. A large part of why I'm even asking this is so that I can get things documented properly so that a server upgrade won't cause catastrophe.

    You are correct. Stopping the service will cancel any job that is currently running. Stopping and restarting the service when no jobs are executing should not impact the system at all.

    One side note, even when executed as a scheduled task, backups are always executed in the context of the SERVER service (not the agent service). I always set up both the SERVER service and the SQLAGENT service to run under the same domain account. It just makes life easier.
    Yeah, MSSQLSERVER and SQLSERVERAGENT are both set up to run as the same user. At least we've done SOMETHING right

    Thanks for all the help, Scott.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by hmscott
    Yep! I already tried upselling that one. And yes, it is MUCH easier to configure and much more reliable as well. I HATE SQL Agent Mail.
    oops, missed that in your post.

    Glad you like Database Mail though. I guess sometimes hard work pays off! I fixed so many bugs in that component, I don't want to think about it anymore...

  11. #11
    Join Date
    Sep 2006
    Posts
    17
    OK, last night my first notification worked. I set up a notification on the backup job to send whenever the job fails. But it sends me just that. No other details, like the job Report.

    OK, fair enough... So I go to set a rule on the Database Maintenance plan to automatically send a report. (like this --> http://img.photobucket.com/albums/v1...-/sqlmail1.jpg) But this will send for every completion of a data or transaction log backup (success or fail) which runs every 15 minutes & 99.9% of the time will be a success. This will quickly lead to an unmanageable number of emails.

    So what I would like to do is have some combonation of these two methods where I can send the report, but specify it to ONLY send when there is a failure. How can something like this be done?

Posting Permissions

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