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='literat
 urerequests@mycompan
y.com',
 @subject='Literature
 Request',
 @message='Literature
 Requests  - contact xxxx or
yyyy  if there are problems or questions   ',

 @attachments='\\myse
 rver\stage_data\lite
rature_request.XLS',
 @copy_recipients='sq
 ladministrator@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='literat
 urerequests@mycompan
y. com',

>                      @subject='Literature
 Request',
>                      @message='Literature
 Requests  - contact xxxx or yyyy
> if there are problems or questions   ',
>
>  @attachments='\\myse
 rver\stage_data\lite
rature_request.XLS',
>                      @copy_recipients='sq
 ladministrator@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='literat
 urerequests@mycompan
y.com',
>                      @subject='Literature
 Request',
>                      @message='Literature
 Requests  - contact xxxx or yyyy
> if there are problems or questions   ',
>
>  @attachments='\\myse
 rver\stage_data\lite
rature_request.XLS',
>                      @copy_recipients='sq
 ladministrator@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
>

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@microso
ft.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:
>