DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
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 st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> 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 st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> 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 st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm