Scheduled DTS Package fails between SQL and Oracle

[mercurymik]

I figure I should start my own thread on this one. I have a SQL Server 2000 on a Microsoft Server 2003 box. And I have Oracle 9i on a Microsoft 2000 Clustered Server. I am trying to transfer data from a SQL server table to an Oracle table.

I have installed an Oracle 9i client on the server and configured an actual Oracle ODBC (not MS ODBC for Oracle). I also have a SQL Server ODBC.

I have created a DTS package and defined a "Transform Data Task" that takes data from the SQL Server and appends it to an Oracle Table. That is all this package does.

I can run this package manually from the SQL Server or my PC (also has Oracle Client)and it works fine. When I schedule the package it fails. I have had to define the the full path to the DTSRun.exe in other scheduled packages, and I did the same for this one.

Here is the error:
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
DTSRun: Loading...
DTSRun: Executing...

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
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 0 (0)

Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0


DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete.


A search on Microsoft's sight on the 80004005 error brings up a lot of generic, can't find the ODBC results. A search for SQLAllocHandle on SQL_HANDLE_ENV failed, brings up some Blog bulletins.

Any ideas? Thanks.

 

[mrdenny]

Sounds like the Oracle drivers aren't setup correctly. Can you log into the servers console and run the DTS Package? If so what are the results?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mercurymik]

Yes, I can run the DTS Package from the Enterprise manager console on the SQL Server and it works fine. As long as I manually run the DTS Package the data is transferred to the Oracle database. When I schedule the package, the scheduled job fails with the error. What I find interesting is that the error seems to indicate it is the SQL Server ODBC that is throwing the error. But, we all know that sometimes errors are not reported correctly. I have several other packages scheduled using the SQL Server ODBC and they work fine. The new part to this package is the Oracle ODBC. I suspect my true error lies in the Oracle product. The question remains, why can I manually run a DTS package successfully, but I cannot schedule it?

I have toyed with the user by making the SQL Agent run as me, I am a Domain Administrator. That did not help.

I could understand if the package did not work, but it does work, except when it is scheduled as a job.

Thanks

 

[mrdenny]

Sounds like some funky problem with the Oracle drivers. What driver are you using to connect to the Oracle database, the generic SQL ODBC or the Oracle Specific ODBC driver?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mercurymik]

I am using the Oracle specific ODBC that was installed with the Oracle Client. It is the SQORA32.dll version 9.02.00.00.

Thanks again

 

[mercurymik]

This problem occurred because the server needed to be rebooted after the installation of the Oracle Client. The reboot was not requested after the client install, like most programs. What is interesting is that I could manually run the DTS package successfully, but, I could not schedule it. Once I rebooted the server, the scheduled package ran great. I found an obscure note in a Microsoft article that hinted toward the need to reboot after an Oracle Client installation.