SQL Server Agent + SQL Mail

[rob_keith]

Hi,
I've been able to successfully configure SQL Server Agent to send
notification emails to an appropriate Operator in the event of an alert
being fired. This all works fine on one condition - that Outlook client
is running on the server.

If Outlook is not running the alerts still be fired (i.e. the Occurance
Rate still increases as and when expected), but the emails being
generated will not be sent until the next time Outlook is opened.

Clearly it's unwise to rely on Outlook being open on the server at all
times, so I was wondering if anyone knows of a workaround to ensure
these emails are processed immediately?

I'm using SQL Server 2000 SP4 and Outlook 2003.

thanks,
Bob.

[Stu]

Hi Bob,

SQLMail is one of my favorite things to hate about SQL Server; it's
difficult to trace why it doesn't work becuase so much of it has
nothing to do with SQL Server at all. Here's some of the things you
can check:

First, you must be running Exchange in order for it to work correctly.
In Outlook, under the Options, Mail Setup, verify that the Send
Immediately When Connected box is checked. Under the Send/Receive
option, you may have to schedule an automatic send/receive every few
minutes.

In essence, it sounds like an Outlook problem, not a SQL problem. You
may have to bring in an Exchange expert on this one.

Hope that helps, somehow.

Stu

[rob_keith]

Hi thanks for the response.

That was the sort of reply I was hoping for, though I don't have the
means to check the exchange server for such setting so I'll have to get
someone else to check it out.

All the best.
Bob.

Stu wrote:

Hi Bob,

SQLMail is one of my favorite things to hate about SQL Server; it's
difficult to trace why it doesn't work becuase so much of it has
nothing to do with SQL Server at all. Here's some of the things you
can check:

First, you must be running Exchange in order for it to work correctly.
In Outlook, under the Options, Mail Setup, verify that the Send
Immediately When Connected box is checked. Under the Send/Receive
option, you may have to schedule an automatic send/receive every few
minutes.

In essence, it sounds like an Outlook problem, not a SQL problem. You
may have to bring in an Exchange expert on this one.

Hope that helps, somehow.

Stu

[Greg D. Moore \(Strider\)]

"Stu" <st**************@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Hi Bob,

SQLMail is one of my favorite things to hate about SQL Server; it's
difficult to trace why it doesn't work becuase so much of it has
nothing to do with SQL Server at all. Here's some of the things you
can check:

First, you must be running Exchange in order for it to work correctly.
This is not true. This keeps getting repeated, but is inaccurate.

It is true that Outlook has to be setup in Corporate/Workgroup mode however.
My suggestion (among others) if you're NOT using Exchange is to install an
SMTP server directly on the SQL Box and lock it down so only that box can
send mail through it.

Setup Outlook 2000 (I don't believe 2003 will work at all) to send through
that SMTP server. This eliminates a whole class of problems where the
MAPI32SP.DLL can get wedged if it loses contact with the SMTP server.

After that, there's a KB article on MS's site about how to properly config
Outlook. Follow ALL of it.

Some other advice:
Make sure ALL the mailboxes (including SENT, OUTGOING, etc.) are empty. No
idea why this makes a difference, but it helps.

Also, make sure you can send it from the account SQLAadmin and SQL Server
are running under. (i.e. log in as that user and send an email.)

Then log out.

THEN start up SQLSERVERAGENT.

I will agree with Stue, it's one of the things I really dislike about SQL
Server.

However, I've found that with moving the SMTP server locally, I've had zero
problems once I got it working. That alone has made a huge difference in my
stability.
In Outlook, under the Options, Mail Setup, verify that the Send
Immediately When Connected box is checked. Under the Send/Receive
option, you may have to schedule an automatic send/receive every few
minutes.

In essence, it sounds like an Outlook problem, not a SQL problem. You
may have to bring in an Exchange expert on this one.
I'd disagree. I've seen exactly this behaviour and it appears to have to do
with how SQL Server accesses the config via MAPISP32.DLL.


Hope that helps, somehow.

Stu