October 5, 2007 at 6:49 am
Hi,
I am trying to setup SQL Mail to work with Outlook 2007. I have an email account created on the SQL Server using the same domain account as the one used to start the SQL Server service. I can send and receive email using the Outlook 2007 client and I have set the profile name for SQL Mail under Support Services. The Test is also successful. Yet there is no green arrow to indicate that SQL Mail is running? I've also set the profile under Management for SQL Server Agent and tried to run a job which will email an operator on completion, but no email appears. Any ideas for what I can try to find out what the problem is?
Thanks.
Steve.
October 5, 2007 at 9:20 am
Basic question, did you start mail?
In SQL you need an xp_startmail to get things going and open the profile before you can xp_sendmail.
October 5, 2007 at 9:41 am
I was under the impression that it would start itself whenever mail was to be sent? I got that from here (Q17). I get these messages also in the error log.
I ran the xp_startmail and got the message SQL Mail session started, but still no green arrow (refreshed EM). Still no email when running a test job either. :unsure:
October 7, 2007 at 9:23 pm
It doesn't always start itself. Might be more consistent in 2000, been awhile since I've worked with this, but I know it used to need to be started.
There are two mail systems. One for SQL Server (using the SQL Server database engine service account) and one for SQL Agent, using that service account.
Did you log on with the SQL Service account and run Outlook, making sure it works?
Also, this might be silly, but is SQL running under a domain account? Are you using Exchange? A local account shouldn't work with Exchange.
October 8, 2007 at 3:28 am
Some progress made...
I also have another SQL 2000 server with SQL Mail setup using Outlook 2000. This one I managed to get working (after changing to a domain account for starting the SQL Server service). It works fine, however there is still no green arrow. I think the green arrow is an urban legend 😀
Back to the other SQL Server 2000 running Outlook 2007 though...
Our email server is POP3/SMTP.
This server does have a domain account for starting the SQL Server service. I've logged onto the server using this account and created the Outlook profile. Also successfully sent a test message from Outlook to this same Outlook account. Now, when I exec the xp_startmail, I get the message 'SQL Mail session started'. I can then exec the xp_sendmail and get the message 'Mail sent'. It is also possible to just exec the xp_sendmail and this automatically starts SQL Mail. However, the message never arrives... that is until I log back onto the SQL Server and actually start Outlook 2007. Then I receive the message.
There was another Microsoft Knowledge Base article here which, under the section 'How To Set Up SQL Mail With An Internet Mail Server', mentions that you can use Outlook 2002 as long as you are willing to keep the Outlook client open on the server at all times.
When trying to open the Outlook 2007 client on the SQL Server, I get an error message saying the .pst file cannot be opened, so I had to exec xp_stopmail first before opening Outlook. But now when I try to exec xp_startmail I get an error:
SQL Mail session started.
Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x8004011d
Also get an error with exec xp_sendmail:
Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
So, now I'm a bit stumped again :crazy:
Gonna try look at those error messages next, but if anyone has any other ideas....yes please 🙂
October 8, 2007 at 7:08 am
you might do better to get a smtp mail proc - there's bound to be several on this site.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 7:51 am
I know I've seen the green arrow, but can't remember what trick to use.
There are issues with sending mail SMTP via Outlook. For some reason they "stick" in the outbox and don't send unless you really interactively log in. My apologies, but I can't find a fix now and can't remember what will make this work. I know that it can work, but I'm not sure if there's a difference between Outlook 2000 and 2007. I didn't work much with 2002 or XP (Office) on this.
Gert Drapers (MS developer now), wrote an XP, XP_SMTPMail, that is highly recommended and used by many people. http://www.sqldev.net/xp/xpsmtp.htm
October 8, 2007 at 7:51 am
BTW, both of these create recovery issues since you have to install something on a new server in a DR situation. Make sure you update documentation and keep copies of this software (either office or the xp) somewhere off the server.
October 8, 2007 at 9:16 am
you need to make sure your mail admins keep the smtp mail queues tidy as well if you use smtp. I've got a couple of stored procs I use - probably easier than using mapi.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 9, 2007 at 2:10 pm
You need to install Outlook 2000. End of story.
Outlook 2000 is the only MAPI client that can run without opening the Outlook application.
March 5, 2021 at 7:03 am
This was removed by the editor as SPAM
March 18, 2021 at 7:25 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply