SQL Mail vs SQL Agent Mail | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Mail vs SQL Agent Mail

How is "SQL Agent Mail" different from "SQL mail"?
Microsoft states that SQL Mail is not supported on SQL Server 2000 64-bit but that SQL Agent mail is supported. As I understand, the SQL Agent controls scheduled tasks/jobs. Most often the properties of a SQL Agent job include a notification to an operator if the job fails or passes.
However, the log record of these jobs reports:
quote:Emailing report to operator ‘david’
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 18038: [Microsoft][ODBC SQL Server Driver][SQL Server]SQL Mail does not work with the 64-bit version of SQL Server
Why would the SQL Agent be using SQL mail and not SQL Agent Mail to send notifications?
Under what conditions would the SQL Agent actualy use SQL Agent mail?

One of the KBA refers SQL Mail is not currently supported in Microsoft SQL Server 2000 64 bit. For more information, see the SQL Server 2000 (64-bit Edition) Books Online. However, SQL Agent Mail in SQL Server 2000 64 bit can be configured remotely by SQL Server Enterprise Manager if your client connects remotely by using SQL Server 2000 Service Pack 3.0 or later.

SQL Mail looks for a MAPI profile under the user account the MSSQLServer service is running under, SQLAgent Mail looks for a MAPI profile under the user account the SQLSERVERAGENT runs under. In addition, any jobs which require access to network resources will run under the context of the user account specified for SQLSERVERAGENT.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I managed to solve my problem, but I’m not really sure why these changes were necessary to fix things: During my Windows 2003 installation on the server, I originaly created one SQL Mail profile on my Outlook Express 6 mail client, using a Domain administrator login.
I then later created a second Windows login on the domain for exclusive SQL Server use to manage the SQL Server services. I then also again configured an account in Outlook Express for use by the second Windows login account. Both accounts have local admin rights on the server and I have no problems sending mail manualy through Outlook Express using either login account. My SQL Agent is now able to send mail correctly when the service is started using the first login account, but cannot send mail when started using the second account, even though both accounts have mail accounts/profiles and equal local and domain rights. What is puzzling here is that:
When starting SQL Agent with the second account; the service starts successfully, the SQL Agent reports that it can start and stop a mail session successfully using that profile, and displays "Message was successfully sent" when sending test emails to operators. However, no mail arrives at the recipient, the network mail server has no record of the mail, and any scheduled SQL Agent job that attempts to notify an operator becomes stuck in a ‘Performing Completion Operations’ status. None of these problems occur when using the first login. From this experience; my advice to anyone configuring a mail profile on 64-bit Edition is to configure one profile for one windows account only and stay with it. Some additional information I’ve learned is that:
If you create a Database Maintenance Plan on SQL Server 64-bit edition, the Reporting tab that allows you to ‘Email report to operator’ makes use of xp_sendmail instead of the SQL Agent Mail notification and so that mailed report will not function in 64-bit Edition. You can add an email notification to the SQL Agent task that will notify of a success or fail of the maintenance task but you cannot receive the attached report file as would normaly be possible with 32-bit Edition.