Is my DTS package failing because of a table drop?

[Stevent]

I have a stored procedure that drops a table if it exists, re-creates
it, and then inserts rows based upon a select query. Pretty standard
stuff. Then I have a local DTS package that exports the resultant
table to an Excel spreadsheet on a network share with "Everyone"
permissions.

The sp and the DTS package are scheduled to run every 24 hours. The
DTS package fails when run through the Enterprise Manager job
scheduler.

I can run the sp and the DTS manually, both from my computer and from
the server, with success, multiple times. I don't think permissions
are an issue but I cannot figure out why the DTS fails when run from
job scheduler.

I've tried using DELETE FROM, WHERE instead of DROP TABLE. I've tried
plain old DELETE FROM. I've even tried TRUNCATE TABLE. No matter
which method I use to clear the data from the previous 24-hour period,
the DTS package fails when run through job scheduler.

Any suggestions?

[Russell Fields]

Steven,

From your mention of Enterprise Manager, I assume that you are using SQL
Server 2000.

When you run the DTS manually, you run with your login's rights. Running a
DTS package manually says nothing about the overall security context when
scheduled through SQL Agent.

We you run DTS from a scheduled job how it runs depends on who owns the SQL
Agent job:
1 - a sysadmin - Runs as the SQL Server service account.
2 - a non-sysadmin - Runs as the SQL Agent Proxy Account.

Unless you set up a username and password in your DTS connections, you will
be running as one of those two accounts. If that is the case, particularly
for the SQL Agent Proxy account, check to see if it has the needed rights.
(Look at: xp_sqlagent_proxy_account.)

RLF

Post by Stevent
I have a stored procedure that drops a table if it exists, re-creates
it, and then inserts rows based upon a select query. Pretty standard
stuff. Then I have a local DTS package that exports the resultant
table to an Excel spreadsheet on a network share with "Everyone"
permissions.
The sp and the DTS package are scheduled to run every 24 hours. The
DTS package fails when run through the Enterprise Manager job
scheduler.
I can run the sp and the DTS manually, both from my computer and from
the server, with success, multiple times. I don't think permissions
are an issue but I cannot figure out why the DTS fails when run from
job scheduler.
I've tried using DELETE FROM, WHERE instead of DROP TABLE. I've tried
plain old DELETE FROM. I've even tried TRUNCATE TABLE. No matter
which method I use to clear the data from the previous 24-hour period,
the DTS package fails when run through job scheduler.
Any suggestions?