Connecting to Oracle Server

Please can someone help.
I have a DTS job that uses datapump to extract data from
an Oracle Server to my SQL Server. When executing the DTS
direcly from Enterprise manager there is no problem. When
running it from a Command screen using dtsrun there is no
problem.
The problem occurs when I run the DTS from a stored
procedure, and the stoored procedure calls the DTS using
exec xp_cmdshell 'dtsrun /S(local) ...'
I get the error below:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -
2147467259 (80004005)
Error string: [Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_ENV failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
I tried reinstall the Oracle Client access software but
the problem remains.
I have same set up installed on another server and there
is no such problem. And the set up on both servers are
identical ( in terms of versions etc )
Also, other DTS packages using datapump have no problem
when using data source other than Oracle.
Set up:
SQL Server 2000 SP3 818
Windows 2000 Server SP4
Any help would be most appreciated.
Thanks
AlexHi
It is not clear if this is being run as a job or directly from QA? If it is
a job, then check the account that is being used for the agent service.
John
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:17d7b01c44a22$32548580$a601280a@.phx
.gbl...
> Please can someone help.
> I have a DTS job that uses datapump to extract data from
> an Oracle Server to my SQL Server. When executing the DTS
> direcly from Enterprise manager there is no problem. When
> running it from a Command screen using dtsrun there is no
> problem.
> The problem occurs when I run the DTS from a stored
> procedure, and the stoored procedure calls the DTS using
> exec xp_cmdshell 'dtsrun /S(local) ...'
> I get the error below:
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -
> 2147467259 (80004005)
> Error string: [Microsoft][ODBC Driver Manager] Driver's
> SQLAllocHandle on SQL_HANDLE_ENV failed
> Error source: Microsoft OLE DB Provider for ODBC Drivers
> I tried reinstall the Oracle Client access software but
> the problem remains.
> I have same set up installed on another server and there
> is no such problem. And the set up on both servers are
> identical ( in terms of versions etc )
> Also, other DTS packages using datapump have no problem
> when using data source other than Oracle.
> Set up:
> SQL Server 2000 SP3 818
> Windows 2000 Server SP4
>
> Any help would be most appreciated.
> Thanks
> Alex
>
>|||Hi John
Thanks for your reply. I have tried both running as a job
and under Query Analyser - with same result. All users the
DTS run under have got sa permission.

>--Original Message--
>Hi
>It is not clear if this is being run as a job or directly
from QA? If it is
>a job, then check the account that is being used for the
agent service.
>John
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
> news:17d7b01c44a22$32548580$a601280a@.phx
.gbl...
DTS[vbcol=seagreen]
When[vbcol=seagreen]
no[vbcol=seagreen]
Drivers[vbcol=seagreen]
>
>.
>|||Hi
Googling for "80004005 dts oracle" gives lots of results, most are referring
to permissions and some indicate it may be a timeout issue.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:180a001c44a2a$dad60f00$a301280a@.phx
.gbl...[vbcol=seagreen]
> Hi John
> Thanks for your reply. I have tried both running as a job
> and under Query Analyser - with same result. All users the
> DTS run under have got sa permission.
>
> from QA? If it is
> agent service.
> message
> DTS
> When
> no
> Driverssqlsql