DTS to Oracle problem

#1

make sure that the account the SQL Agent is using has sys-
admin rights or it will not be able to access network
resources.

Quote:

-----Original Message-----
Hello. I am using SQL Server 2000 and have a BIZARRE DTS
problem. I
have a package that has two tasks. The first task deletes
all the data
in a certain table. The second task is a datapump between
an Oracle
database and the table just deleted.

I am using the native Oracle Provider for OLE DB, not the
Microsoft
Oracle provider. Here is the list of what's going on.
1. I can run the package manually from the DTS designer
with no
problem.
2. If I schedule the job(setting the owner to the same ID
as the SQL
Service Agent is running as), it completes the first
step, but spins
on the second step.
3. If log onto the server that the SQL Server is
physically on and log
in as the same ID as the SQL Service Agent is, and
manually run the
package, it works too.
4. Run the package as a job, under that same ID, it spins
on the
second task again.
5. Still logged in as the admin, I go to a command prompt
and do
DTSRUN to run the package. It works.
6. Schedule the package using the SAME DTSRUN command, it
spins.

I've had an Oracle DBA watch the connections and when the
package is
run manually, he is able to see the domain ID open a
connection(at the
OS level) and then he sees the package log into the
database using
the(different) database ID. When I run the job, he sees
nothing try to
connect.

Does anyone have ANY suggestions on where to go from
here? I thought
it was some kind of permissions issue, but since I can
log into the
SQL box as the admin and run the package, I can't imagine
what else is
wrong. Especially since I can manually run the DTSRUN
command and get
it to work. I'm not sure what is being done behind the
scenes with the
scheduled jobs, but I'm lost.

Thanks for any ideas!
.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS to Oracle problem

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS to Oracle problem - 09-23-2004 , 04:56 PM