Setting Up SQL Mail
-
Hello,
I have been trying to set up my sql mail all day today. So far, I have successfully changed the sql services to use a proper logon account and not the "localsystem" account. I then set up profile in Outlook 2000. I went to the sql mail settings in the "support services" tab in enterprise manager and I was able to set the profile and test it successfully.
I tried to go into a job and set up an operator but it didn't work and said that the sql server mail agent had not been started. I did some research and found that I could use the xp_startmail to start the service. I went into query analyser and tried to run this and got the following error:
SQL Mail session started.
Server: Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
So, I'm kinda stuck as to what to do now...anybody know how to help me from here? I have tried looking up some knowledge base stuff and it either confuses me or doesn't relate to what I'm doing.
Thanks!
-
Ok. First let me try and clarify some of the confusion and then we'll tackle the real problem at hand. SQL Mail and SQL Server Agent Mail are separate and must be configured separately.
The work you've done, in order to get emails for jobs (and the error with respect to the operator) is for SQL Agent Mail. You configure this by modify the SQL Server Agent properties.
The extended stored procedure xp_startmail is for SQL Mail. You set up SQL Mail by configuring the server properties (Server Settings tab and it's about the middle of the dialog box). The error you received is a logon failure. If you don't have SQL Mail configured to use a profile or the profile is incorrect, you'll get that error.
Ok. Now let's look at SQL Server Agent Mail, which it sounds like you are interested in. The Test button just checks to see if you can access the profile. It doesn't actually start anything up. So you could have a successful test yet still fail. One place that might occur is if the default mail client is still configured to use Outlook Express. In that case, you wouldn't e able to get SQL Server Agent Mail to start. You can check this by logging on to the server with the account being used to run SQL Server Agent. Right-click on Internet Explorer and choose Properties. Take a look at the Programs tab and see what the default client is for E-mail. If it's not Microsoft Outlook, set it to that.
More troubleshooting advice:
K. Brian Kelley
@kbriankelley -
Occasionally, when setting up SQLMail for the first time, I just can't get it to work until the server is restarted. So, if you are still having problems after trying what Brian suggests, you may have to reboot.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks for your reply... the default mail client is set to be Outlook 2000 and not express.
Second, how do I modify the Sql Agent Mail properties? If I go into the services and open up the properities for the SqlAgent it doesn't give me anything...
I am still confused because when I try to set up the Operator and it says that sql agent mail isn't running and I go to do the xp_startmail it says that the service is already running however, I still get that little error when it tries to do the xp_sendmail ...
I suppose I am very confused on this subject. I have done some research and I keep getting the same FAQ's thrown at me and the same troubleshooting things but it doesn't exactly tell me what is what and how to configure everything...
Thanks in advance.
-
I have rebooted many times... and just for clarification...this is Sql Server Personal Edition and it is not running on a server box.
-
In Enterprise Manager, expand "Managment". Right click on "SQL Server Agent." On the "General" tab, select the email profile from the drop down list. Then click "Test." Then click "OK." You might be prompted to restart SQL Agent.
I usually log in as the start up account and run Outlook to create the profile before attempting the above.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Just as a clarification, xp_startmail and xp_sendmail belong on the SQL Mail side. This is through SQL Server (and the SQL Server service itself). SQL Agent Mail (for SQL Server Agent) doesn't use these stored procedures at all. It is confusing because one would think to just have one mail system for all of SQL Server. However, some folks run the SQL Server service under a different account than the SQL Server Agent service. And they require mail. That's why there is a separation.
As for getting SQL Server Agent Mail up and running, Kathi's steps are what I follow as well. Log in as the account. Create (or verify the profile). Then go into Enterprise Manager and configure SQL Agent Mail.
K. Brian Kelley
@kbriankelley -
Also, you said that you changed the account for the "services." Make sure that you did change the account for SQL Agent.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
LOL, duh... I'm blind!
Ok, I modified the sql server agent... however, when I go in to create an operator and click on the "test" for the email name it is just sitting there and has been sitting there for like 15 mins... I do put an email address there right?
-
Ok nevermind, it finally came up... it worked! Woo...
But the net send option said it failed...but didn't say why.
-
Thanks a bunch! I was definately getting SQL Mail and SQL Agent Mail confused. I was able to get it set up and sending messages!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply