Agent job hanging on xp_sendmail

I have a batch job running under SQL Server Agent that reads info from a
table, uses an excel sheet as a template, and creates an output excel
spreadsheet, that gets then sent to users.
xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
@.subject='Literature Request',
@.message='Literature Requests - contact xxxx or
yyyy if there are problems or questions ',
@.attachments='\\myserver\stage_data\lite
rature_request.XLS',
@.copy_recipients='sqladministrator@.mycom
pany.com'
For some reason, this job every once in a while will hang on the step
above. This is all the code that's in that one step, so it's not doing
anything else.
The job simply hangs and will not complete, and will not generate any
error messages. Stopping Agent and re-starting does not have any
impact. I am preparing to stop SQL on that server and re-start - but I
don't know if that'll fix the problem.
The version of SQL we're on is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
O/S is Windows 2003, SP1
Any ideas as to why this is hanging? Everything else on this server
appears to be functioning normally.
Any help appreciated. I am posting this in several forums, because I'm
not sure where this question actually belongs - whether it's a
programming or dts issue, or a setup issue.
Thanks,
SC> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
Perhaps a prompt is being raised to enter credentials to access the share.
Or perhaps the network is somewhere short of reliable. Are you sure the
user SQL Server Agent is running as has full access to the share? Is it
possible it works when the user is logged in, but not otherwise? Would it
be possible to isolate the share as the source by temporarily creating the
XLS file on the local SQL Server machine, and see how long you can run
without error? Have you also isolated Outlook application/profile problems
and/or Exchange authentication issues by attempting to use a more simple
delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )?|||No need to cross-post. If it is in the wrong group, an MVP will usually
direct you to where it goes. .setup or .server would be appropriate in this
case.
This is a well-known problem with SQL Mail and SQL Agent Mail. What is
happening is the MAPI interface is stuck. This is usually because Outlook
needed to pop a dialog box but was not running on the console. There is a
long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
service, but we won't get into that here.
You have a couple of options. You can leave the console of the server
logged in as the SQL service account and let Outlook run all the time or you
can replace the native SQL Mail components with SMPT-based equivalents. I
usually use the latter option. It isn't formally recommended by Microsoft,
but every support engineer in PSS knows about the replacement XPs. They
aren't exact drop in replacements, you will have to recode some stuff, but
they work very well.
Here is the link:
http://www.sqldev.net/xp/xpsmtp.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>I have a batch job running under SQL Server Agent that reads info from a
>table, uses an excel sheet as a template, and creates an output excel
>spreadsheet, that gets then sent to users.
> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
> The job simply hangs and will not complete, and will not generate any
> error messages. Stopping Agent and re-starting does not have any impact.
> I am preparing to stop SQL on that server and re-start - but I don't know
> if that'll fix the problem.
> The version of SQL we're on is:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> O/S is Windows 2003, SP1
> Any ideas as to why this is hanging? Everything else on this server
> appears to be functioning normally.
>
> Any help appreciated. I am posting this in several forums, because I'm
> not sure where this question actually belongs - whether it's a programming
> or dts issue, or a setup issue.
> Thanks,
> SC
>|||Aaron Bertrand [SQL Server MVP] wrote:
>
<<Perhaps a prompt is being raised to enter credentials to access the
share. >>
This should not be the case. The profile generating the email has full
administrator rights to that box.
<<Or perhaps the network is somewhere short of reliable.>>
I cannot rule this out, yet, but, I can ping the server, the server can
ping anywhere I want - all fine - but the step goes in & just hangs on it.
<<Are you sure the user SQL Server Agent is running as has full access
to the share?>>
Yes - the same account that I use to generate the email is the same one
that controls sql agent.
<<Is it possible it works when the user is logged in, but not otherwise? >>
No - it has run the entire month of January without them being logged
in, and only hangs up each time I run it today.
<<Would it be possible to isolate the share as the source by temporarily
creating the
> XLS file on the local SQL Server machine, and see how long you can run
> without error?>>
I don't know what that would accomplish - in looking at the history,
it's run many times throughout January without a hiccup until today.
<< Have you also isolated Outlook application/profile problems
> and/or Exchange authentication issues by attempting to use a more simple
> delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )? >>[/c
olor]
I have not done this, but will look into it. It only does it
intermittently, and stopping SQL and re-starting, or restarting the
server seems to be the only thing that fixes it. I know it is occurring
on two other servers that use the same xp_sendmail stored procedure, but
use different account logins.
Thanks for the link - I will explore that option - hopefully it isn't
re-inventing the wheel to put that piece in.
BC|||> I don't know what that would accomplish - in looking at the history, it's
> run many times throughout January without a hiccup until today.
So maybe today the share is having a problem! If you put the file locally
and it runs, it will rule that out, will it not? And if it suddenly works,
doesn't that give you some information too?
A|||Geoff,
I'm curious as to the history of why SQL insists on using a MAPI client -
any pointers on where to find more historical info as to the whys and
wherefores?
Thanks
"Geoff N. Hiten" wrote:

> No need to cross-post. If it is in the wrong group, an MVP will usually
> direct you to where it goes. .setup or .server would be appropriate in th
is
> case.
> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
> happening is the MAPI interface is stuck. This is usually because Outlook
> needed to pop a dialog box but was not running on the console. There is a
> long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
> service, but we won't get into that here.
> You have a couple of options. You can leave the console of the server
> logged in as the SQL service account and let Outlook run all the time or y
ou
> can replace the native SQL Mail components with SMPT-based equivalents. I
> usually use the latter option. It isn't formally recommended by Microsoft
,
> but every support engineer in PSS knows about the replacement XPs. They
> aren't exact drop in replacements, you will have to recode some stuff, but
> they work very well.
> Here is the link:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Blasting Cap" <goober@.christian.net> wrote in message
> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||Nothing that is written down. When Microsoft rewrote SQL Server at the 7.0
release,MAPI was the corporate messaging standard. Someone within Microsoft
was supposed to provide a service-level MAPI client, which never
materialized. MAPI didn't fly and was eventually semi-abandoned, but SQL
2000 had to support it for legacy reasons. By the time it was clear that
MAPI was not the wave of the future for mail, SQL 2000 was locked in,
feature-wise. Given that SQL 2000 was the oldest current release server
product in the MS inventory until SQL 2005 was released late last year, it
makes sense that it had more hooks to obsolete technology.
Nothing malicious, just normal feature introduction and removal compounded
by different product life cycles.
You want another example, did you know you could set up a 6.5 SQL server to
accept a query via email, execute it, and return the results. Imagine how
bad a security hole that would be today.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:B19D645B-D373-40B3-8591-92649579CDDF@.microsoft.com...
> Geoff,
> I'm curious as to the history of why SQL insists on using a MAPI client -
> any pointers on where to find more historical info as to the whys and
> wherefores?
> Thanks
> "Geoff N. Hiten" wrote:
>