October 26, 2004 at 8:27 am
Hi,
I'm sure this is one of those problems that's simple to fix, once you know how, but I am having trouble sending mail vis a DTS apckage when I run it as part of a scheduled job.
If I run it straight from the package it completes without any problem but when it is scheduled and run using the SQL Server Agent's profile, I get the following error:
Executed as user: Europe\eoclab1. ...nStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSSendMailTask_2 DTSRun OnError: DTSStep_DTSSendMailTask_2, Error = -2147220352 (80040480) Error string: Logon failed: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 9100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 9100 DTSRun OnFinish: DTSStep_DTSSendMailTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSSendMailTask_4 DTSRun OnError: DTSStep_DTSSendMailTask_4, Error = -2147220352 (80040480) Error strin... Process Exit Code 2. The step failed.
Any help much appreciated?
Regards,
Jay.
October 27, 2004 at 12:55 am
Jay,
Ensure the profile name in the send mail task in your DTS package is the same profile being used via SQL Server Agent.
When you run the package manually it uses your profile. When the package is scheduled it uses the SQL Server Agent profile.
Jill
October 27, 2004 at 1:42 am
Thanks very much for that, Jill but I'm afraid the server I'm using does not have MAPI mail client so I'm going to have to use xp_sendmail to do the work. However, when I try to use this I have the same kind of issue as before. I can run the execute sql task containg the xp_sendmail statement without any problems but when I schedule it, it fails with a DCP transport error?
Any ideas?
Thanks,
Jay.
October 27, 2004 at 5:35 am
What Jill says is 100% correct.
I noticed that the log shows you are in fact using a SEND MAIL task - DTSStep_DTSSendMailTask_2 (not an EXEC SQL task) so when you open the DTS package and right click the task and select Exec Step it works - why? Becuase when you open the DTS editor to run the package (or steps) interactively it runs in the context of the machine on which you are working, that is it uses your local machines DTS dlls to run the package. In all likelihood this local machine is your workstation and you have a MAPI client on that workstation.
I bet if you connect to the server using Query Analyser and try and exec xp_sendmail it'll fail also - why no MAPI client on the server (as you said).
If you can't install SQL Mail on the server (and I prefer not to it's dodgy to put client tools on a server IMO) then I'd suggest that you consider using xp_smtp_sendmail instead - it requires no client on the server and is free from http://www.sqldev.net, and suffers none of the issues associated with xp_sendmail. All you do is install the dll and register the xp.
This means you need to change your DTS package to use an EXEC SQL task to fire xp_smtp_sendmail intead of a SEND MAIL task.
Another nice thing about xp_smtp_sendmail is it can't process inbound mail.
October 28, 2004 at 5:50 am
Many Thanks to both of you.
I've registered the appropriate DLL's on the server and changed the DTS package appropriately.
Evertything works fine now.
Regards,
Jay.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply