Problem in emailing to operator in SQLAgent

Hi
I got the following error when I tested emailing to the operator in Sql
Server Agent.
"Error 22022 : SQLServerAgent Error. The SQLServerAgent mail session is
not running; check the mail profile and/or the SQLServerAgent service
startup account in the SQLServerAgent properties dialog."
However, if I stopped and then started the SQL Server Agent again, I can
send email to the operator successfully. The problem happened again when the
server is restarted.
Here are some checks on my server.
- Running SQL2000 (SP4) on SBS2003. Outlook client 2003.
- Mail profile is using Exchange 2003, not POP3
- Mail Profile test in Sql Server Agent is alway OK even though I cannot
send email to operator.
- SQL Server Agent service startup account = SQL server service startup
account = mail profile mail box AD account
- SQL server agent is set to start automatically with Sql Server
Any idea for solving this problem?
EdwinHi Edwin,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood your SQL Agent won't launch smoothly
until you have restarted it. If I have misunderstood your concern, please
feel free to point it out.
First of all, please make sure SQL Server and SQL Agent services are
starting with same domain accounts. Does the domain account has
Administrator privilege?
Secondly, it is really strange that the restart of service will fix this
scenario. Would you pelase check the Event Log to see if there is any more
related information?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
SQL Agent did launch successfully. Scheduled job can run. Testing of mail
profile is OK. Even xp_sendmail can work in query analyzer. The only
problem is cannot email to operator from SQLAgent when needed (such as a job
failed).
As said, SQL Server service startup a/c = SQLAgent service startup a/c = the
AD a/c used to setup the mail profile on the server. The AD a/c is member of
domain administrators group.
No warning or error events are found regarding MSSQLSERVER and
MSSQLSERVERAGENT event source.
This is not an occasional event. It happened in my old SBS2000. After I
migrated it to SBS2003, the problem is still there. Of course the two
servers are using similar settings and so I think some settings cause the
problem.
Any ideas?
Edwin
"Michael Cheng [MSFT]" wrote:
> Hi Edwin,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood your SQL Agent won't launch smoothly
> until you have restarted it. If I have misunderstood your concern, please
> feel free to point it out.
> First of all, please make sure SQL Server and SQL Agent services are
> starting with same domain accounts. Does the domain account has
> Administrator privilege?
> Secondly, it is really strange that the restart of service will fix this
> scenario. Would you pelase check the Event Log to see if there is any more
> related information?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Edwin,
This is a really strange issue that you will have to the restart of SQL
Agent to
Since the machine was restarted, the SQL Agent was also restarted. I am not
sure why it need twice restart.
Would you please perform the steps below and let me know the result?
When the machine was restarted execute the following statements directly in
the
EXECUTE master.dbo.xp_sqlagent_notify 'N', NULL, NULL, NULL, 'E',
N'edwinchan@.nospam.nospam'
--Make sure to replace edwinchan@.nospam.nospam
--with a valid test email destination address
Whether it will send the email as expected?
After this, execute the same statement after the SQL Agent was restarted
twice.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
I got the same error from query analyser.
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: The SQLServerAgent mail session is not running; check
the mail profile and/or the SQLServerAgent service startup account in the
SQLServerAgent Properties dialog.
I have browsed through the event log. No errors or warnings on SQLSERVER
and SQLSERVERAGENT. But I do find two event 101 after server is restarted.
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Service Control
Event ID: 101
Date: 11/18/2005
Time: 7:00:02 AM
User: N/A
Computer: AIMS
Description:
SQLServerAgent service successfully started.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
They occured about 20 seconds apart. Not sure whether this is normal or not.
It seems to me that it is about timing when the SQLServerAgent service is
started. It is just not started at the right time. Will it matter if
SQLServerAgent service is started when Exchange is not completely started?
I have not started SQLServerAgent service yet. What else would you like to
test?
Edwin
"Michael Cheng [MSFT]" wrote:
> Hi Edwin,
> This is a really strange issue that you will have to the restart of SQL
> Agent to
> Since the machine was restarted, the SQL Agent was also restarted. I am not
> sure why it need twice restart.
> Would you please perform the steps below and let me know the result?
> When the machine was restarted execute the following statements directly in
> the
> EXECUTE master.dbo.xp_sqlagent_notify 'N', NULL, NULL, NULL, 'E',
> N'edwinchan@.nospam.nospam'
> --Make sure to replace edwinchan@.nospam.nospam
> --with a valid test email destination address
> Whether it will send the email as expected?
> After this, execute the same statement after the SQL Agent was restarted
> twice.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Edwin,
>> It seems to me that it is about timing when the SQLServerAgent service
is
>> started. It is just not started at the right time. Will it matter if
>> SQLServerAgent service is started when Exchange is not completely
started?
You have installed the Exchange Server on the same machine?
If the Exchange Server is started behind SQL Server Agent's start, the mail
profiler may not get the correct information.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
As said, my server is running SBS2003 and it is assumed to have Exchange2003
and SQL server 2000 running in the same machine. I think all of the SBS
users are doing that way; otherwise why buy SBS' If the problem is caused
by having Exchange and SQL server running in the same machine, it is
obviously a design fault of the product. Is there any work around from
Microsoft? How can I restart SQLAgent Service automatically after each
reboot?
Edwin
"Michael Cheng [MSFT]" wrote:
> Hi Edwin,
> >> It seems to me that it is about timing when the SQLServerAgent service
> is
> >> started. It is just not started at the right time. Will it matter if
> >> SQLServerAgent service is started when Exchange is not completely
> started?
> You have installed the Exchange Server on the same machine?
> If the Exchange Server is started behind SQL Server Agent's start, the mail
> profiler may not get the correct information.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Edwin,
Thanks for the update.
Would you pleaes let me know how to do you create the Mail Profile?
First of all, please understand we do not support installing Exchange
Server with Outlook together.
Microsoft does not support installing Exchange Server components and
Outlook on the same computer
http://support.microsoft.com/?id=266418
However, you could configure your SQL Mail as described below
How to create MAPI profiles without installing Outlook
http://support.microsoft.com/kb/306962/en-us
Secondly, you may add dependency to Exchange Services for SQLAgent Services
or SQL Server Services.
-1- Start -> Run -> services.msc
-2- Find all the service name for each Exchange Servicew
(For example, the service name for Microsoft Exchange POP3 is POP3Svc, you
could see this when you check the properties for each service)
-3- Start -> Run -> Regedit
-4- Open Key DependOnService under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT
-5- Input all Services from Step -2- to DependOnService
-6- Restart the machine to see whether it works
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Yes, Outlook was installed in the server. I have read the article 306962
but found it is not quite user friendly to follow through. First of all I
cannot find NewProf.exe in the Outlook 2003 CD came with my SBS. I looked
into ProfMan2.exe but it just mentioned Outlook 2000 compliant and so I am
not sure it can be used in Exchange 2003 or not. The other approaches looked
quite unfamilar to me because it required to write some programs to do the
job. I hope MS can suggest a more user friendly way to create a mail profile
in Exchange 2003.
Anyway, making the SQLSERVERAGENT service dependent on MSExchangeIS service
solved my problem. Hence it seems it was caused by having SQLSERVERAGENT
started too early.
Regards
Edwin
"Michael Cheng [MSFT]" wrote:
> Hi Edwin,
> Thanks for the update.
> Would you pleaes let me know how to do you create the Mail Profile?
> First of all, please understand we do not support installing Exchange
> Server with Outlook together.
> Microsoft does not support installing Exchange Server components and
> Outlook on the same computer
> http://support.microsoft.com/?id=266418
> However, you could configure your SQL Mail as described below
> How to create MAPI profiles without installing Outlook
> http://support.microsoft.com/kb/306962/en-us
> Secondly, you may add dependency to Exchange Services for SQLAgent Services
> or SQL Server Services.
> -1- Start -> Run -> services.msc
> -2- Find all the service name for each Exchange Servicew
> (For example, the service name for Microsoft Exchange POP3 is POP3Svc, you
> could see this when you check the properties for each service)
> -3- Start -> Run -> Regedit
> -4- Open Key DependOnService under
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT
> -5- Input all Services from Step -2- to DependOnService
> -6- Restart the machine to see whether it works
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Edwin,
It's great to hear it was resolved by making the dependency.
Thanks so much for your suggestions and I will submit your concerns about
mail profile creation via internal resources. In the meanwhile, you are
also encouraged to submit this feeling via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.