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*.
Thanks
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. 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

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

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

DTS and Excell

Help.
Can someone help me with a DTS? I have a curser that creates a temp
table and then sever select statments from the tmp table. I am tryin to
place each out put from the many select statements in its own excel
worksheet.
Hi
"TG" wrote:

> Help.
> Can someone help me with a DTS? I have a curser that creates a temp
> table and then sever select statments from the tmp table. I am tryin to
> place each out put from the many select statements in its own excel
> worksheet.
The way I would probably do this would be for each select statement to
require it's own transformation with the excel file as the datasource and the
destination table being the datasheet that you are inserting into.
HTH
John

DTS and Excell

Help.
Can someone help me with a DTS? I have a curser that creates a temp
table and then sever select statments from the tmp table. I am tryin to
place each out put from the many select statements in its own excel
worksheet.Hi
"TG" wrote:
> Help.
> Can someone help me with a DTS? I have a curser that creates a temp
> table and then sever select statments from the tmp table. I am tryin to
> place each out put from the many select statements in its own excel
> worksheet.
The way I would probably do this would be for each select statement to
require it's own transformation with the excel file as the datasource and the
destination table being the datasheet that you are inserting into.
HTH
John

DTS and Excell

Help.
Can someone help me with a DTS? I have a curser that creates a temp
table and then sever select statments from the tmp table. I am tryin to
place each out put from the many select statements in its own excel
worksheet.Hi
"TG" wrote:

> Help.
> Can someone help me with a DTS? I have a curser that creates a temp
> table and then sever select statments from the tmp table. I am tryin to
> place each out put from the many select statements in its own excel
> worksheet.
The way I would probably do this would be for each select statement to
require it's own transformation with the excel file as the datasource and th
e
destination table being the datasheet that you are inserting into.
HTH
John

DTS and #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperJP,
You are running into problems with the scope of a #temp table. It is
available from the creating code level DOWN. Anything above the block of
code, stored procedure, etc will not be able to see this table. Likewise,
anything that runs in some other connection, for example one used by a DTS
package, will not be able to see the #temp table.
In the SQL Books Online, read the discussion under the CREATE TABLE command
on Temporary Tables. It discusses the scope and life of both kinds of
temporary tables.
RLF
"JP" <JP@.discussions.microsoft.com> wrote in message
news:66B3FF76-EB09-46A0-A9FF-2F6AA24E2EAB@.microsoft.com...
>I have a rather complex SP that creates a #temp table and then populates
>that
> table using several select/inserts/update statements. I need the results
> of
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the
> references
> to be ##table isn't solving the problem.
> Is there commands I can place in the SP to create the external file
> without
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>|||Hi,
I would suggest you to use UDF instead of #TempTable so it is able to be
exported
Ed
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>

Dts

I am trying to copy a table from Access into Oracle using DTS. I let DTS to create the table in Oracle and then populate it with data. It creates the table but as soon as it tries to copy the data I get an error message: Table does not exist. Any idea why?I am trying to copy a table from Access into Oracle using DTS. I let DTS to create the table in Oracle and then populate it with data. It creates the table but as soon as it tries to copy the data I get an error message: Table does not exist. Any idea why?

More of an Oracle question really than SQL. I'm not the expert on Oracle, but I would gues that it has to do with schema ownership. Try qualifying the name of the table in the DTS package with the owner (usually the user id underwhich the table was created).

Regards,

hmscott