SQL Mail and SQL Agent Mail @!~$%!%#^$%&^#&#%

I have been reading everything i can find regarding sql mail and sql
agent mail.

We have a win2k server, sql 2000 and NO, NO, NO exchange server(all up
to date on service packs).

I have tried repeatadly for days to make this work, and have had no
luck so far.

1. I have a valid profile that tests fine in sql agent and via sql
mail test buttons.

2. I have logged in to the box under that new domain account to
install outlook and configure all the remaining options.

3. I have set both the mssqlserver service and the sqlserveragent
service to run under the domain account that coincides with our pop3
email server, and restarted both of them.

4. I have installed outlook 2002, and I can send and receive emails
just fine from our sql server.

5. As soon as I try to test an email from within a job, the test
fails.

All I wanted to do was just be notified if a job fails. The amount of
time lost on what should have been a 30 minute project is
unbelievable!!!

If anyone has some suggestions, other than the obvious, please let me
know.

Thanks,
Dave

re: SQL Mail and SQL Agent Mail @!~$%!%#^$%&^#&#%

I'd like to know this myself... seems worse than configuring the shuttle for
a damn space mission. All we want is a simple email...WTF is up with that?
If anyone can share some pointers we would appreciate it greatly!

TIA - Rob

"Dave" <funkdm1@yahoo.com> wrote in message
news:f5174e0f.0405171200.3ad272ba@posting.google.c om...[color=blue]
> I have been reading everything i can find regarding sql mail and sql
> agent mail.
>
> We have a win2k server, sql 2000 and NO, NO, NO exchange server(all up
> to date on service packs).
>
> I have tried repeatadly for days to make this work, and have had no
> luck so far.
>
> 1. I have a valid profile that tests fine in sql agent and via sql
> mail test buttons.
>
> 2. I have logged in to the box under that new domain account to
> install outlook and configure all the remaining options.
>
> 3. I have set both the mssqlserver service and the sqlserveragent
> service to run under the domain account that coincides with our pop3
> email server, and restarted both of them.
>
> 4. I have installed outlook 2002, and I can send and receive emails
> just fine from our sql server.
>
> 5. As soon as I try to test an email from within a job, the test
> fails.
>
> All I wanted to do was just be notified if a job fails. The amount of
> time lost on what should have been a 30 minute project is
> unbelievable!!!
>
> If anyone has some suggestions, other than the obvious, please let me
> know.
>
> Thanks,
> Dave[/color]

re: SQL Mail and SQL Agent Mail @!~$%!%#^$%&^#&#%

"Dave" <funkdm1@yahoo.com> wrote in message
news:f5174e0f.0405171200.3ad272ba@posting.google.c om...[color=blue]
> I have been reading everything i can find regarding sql mail and sql
> agent mail.[/color]

This is the single most frustrating item I've encountered in SQL Server.

(Though I hear SQL 2005 does away with the requirement for a MAPI mail
client. )

Couple of quick comments/questions:

1) The new domain account is the same one that SQL Agent runs as, correct.
(I know, obvious question. :-)

2) There is no real need for the domain account to be the same on the pop3
mail server (in fact I've used a separate off-site sendmail server on
linux).

3) You've got Outlook 2002 setup correctly (I forget the one setting, but
it's critical... Internet only I think is the name of the one you do NOT
want. YOu want the "Workgroup" or whatever it's called.)

4) Probably too late, but might try Outlook 2000. I've heard 2002 has MORE
problems than 2000 for SQL Mail.

5) Also.. make sure the Outlook client has NO messages in any mailbox,
including the sent, draft, etc. I don't know why, but it seems to be an
issue.

Also, once you've started SQL Server and Agent, in Query Analyzer first do
xp_startmail and observe the resulting message.

Then try xp_stopmail.

If either fails, that would be useful to know.

If you can start/stop it, you're 1/2way there.

Then try xp_readmail. This should show NOTHING.

Then try xp_sendmail and see what happens.

After a minute or so, try xp_readmail. This SHOULD be empty again, but
probably won't be.

At this point see if you can do xp_stopmail.

Not sure if any of this will work, but it may help us diagnose it.

Quite frankly, I've found it to be a black art. When it works, it's great.
Jobs notify you, etc.

When it doesn't, it's a huge PITA.

[color=blue]
>
> We have a win2k server, sql 2000 and NO, NO, NO exchange server(all up
> to date on service packs).
>
> I have tried repeatadly for days to make this work, and have had no
> luck so far.
>
> 1. I have a valid profile that tests fine in sql agent and via sql
> mail test buttons.
>
> 2. I have logged in to the box under that new domain account to
> install outlook and configure all the remaining options.
>
> 3. I have set both the mssqlserver service and the sqlserveragent
> service to run under the domain account that coincides with our pop3
> email server, and restarted both of them.
>
> 4. I have installed outlook 2002, and I can send and receive emails
> just fine from our sql server.
>
> 5. As soon as I try to test an email from within a job, the test
> fails.
>
> All I wanted to do was just be notified if a job fails. The amount of
> time lost on what should have been a 30 minute project is
> unbelievable!!!
>
> If anyone has some suggestions, other than the obvious, please let me
> know.
>
> Thanks,
> Dave[/color]

re: SQL Mail and SQL Agent Mail @!~$%!%#^$%&^#&#%

Hi - I'd just like to add... when you set up the profile you need to
log on to the box as the sql server to do so. Otherwise the profile
magically disappears when the sqlserver logs on.

Also I gather there are some issues with sqlmail and pop3 - eg it
hanging waiting for user input that never gets asked for...



"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@greenms.com> wrote in message news:<lPeqc.212553$M3.141773@twister.nyroc.rr.com> ...[color=blue]
> "Dave" <funkdm1@yahoo.com> wrote in message
> news:f5174e0f.0405171200.3ad272ba@posting.google.c om...[color=green]
> > I have been reading everything i can find regarding sql mail and sql
> > agent mail.[/color]
>
> This is the single most frustrating item I've encountered in SQL Server.
>
> (Though I hear SQL 2005 does away with the requirement for a MAPI mail
> client. )
>
> Couple of quick comments/questions:
>
> 1) The new domain account is the same one that SQL Agent runs as, correct.
> (I know, obvious question. :-)
>
> 2) There is no real need for the domain account to be the same on the pop3
> mail server (in fact I've used a separate off-site sendmail server on
> linux).
>
> 3) You've got Outlook 2002 setup correctly (I forget the one setting, but
> it's critical... Internet only I think is the name of the one you do NOT
> want. YOu want the "Workgroup" or whatever it's called.)
>
> 4) Probably too late, but might try Outlook 2000. I've heard 2002 has MORE
> problems than 2000 for SQL Mail.
>
> 5) Also.. make sure the Outlook client has NO messages in any mailbox,
> including the sent, draft, etc. I don't know why, but it seems to be an
> issue.
>
> Also, once you've started SQL Server and Agent, in Query Analyzer first do
> xp_startmail and observe the resulting message.
>
> Then try xp_stopmail.
>
> If either fails, that would be useful to know.
>
> If you can start/stop it, you're 1/2way there.
>
> Then try xp_readmail. This should show NOTHING.
>
> Then try xp_sendmail and see what happens.
>
> After a minute or so, try xp_readmail. This SHOULD be empty again, but
> probably won't be.
>
> At this point see if you can do xp_stopmail.
>
> Not sure if any of this will work, but it may help us diagnose it.
>
> Quite frankly, I've found it to be a black art. When it works, it's great.
> Jobs notify you, etc.
>
> When it doesn't, it's a huge PITA.
>
>[color=green]
> >
> > We have a win2k server, sql 2000 and NO, NO, NO exchange server(all up
> > to date on service packs).
> >
> > I have tried repeatadly for days to make this work, and have had no
> > luck so far.
> >
> > 1. I have a valid profile that tests fine in sql agent and via sql
> > mail test buttons.
> >
> > 2. I have logged in to the box under that new domain account to
> > install outlook and configure all the remaining options.
> >
> > 3. I have set both the mssqlserver service and the sqlserveragent
> > service to run under the domain account that coincides with our pop3
> > email server, and restarted both of them.
> >
> > 4. I have installed outlook 2002, and I can send and receive emails
> > just fine from our sql server.
> >
> > 5. As soon as I try to test an email from within a job, the test
> > fails.
> >
> > All I wanted to do was just be notified if a job fails. The amount of
> > time lost on what should have been a 30 minute project is
> > unbelievable!!!
> >
> > If anyone has some suggestions, other than the obvious, please let me
> > know.
> >
> > Thanks,
> > Dave[/color][/color]