June 25, 2003 at 10:38 am
I have a DTS package that copies some data from SQL server over to an Oracle server. The packages runs without error if run in DTS Designer but fails on the first step which uses the Oracle ODBC connection when run as a job. Preceding SQL steps run against the local SQL Server execute OK so it seems to be something to do with Oracle connectivity. The error I get is:
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
I have found nothing in the Microsoft Knowledge base about this. Has anyone else seen this and found a fix?
Mark Hottinger
Mark
June 25, 2003 at 12:06 pm
Have you tested the Oracle ODBC connection with something like MS Access? What version of the Oracle ODBC driver are you using?
June 25, 2003 at 3:18 pm
Yes. I can access the oracle database from SQL Plus (the Oracle version of Query Analyzer) and I have no problem connecting. We are using Oracle Client version 8.0.5
Mark Hottinger
Mark
June 30, 2003 at 9:38 am
Are you working from the Server itself or client side? If you are working client side and not physically on the SQL box, it may be that the SQL box does does not have the needed Oracle drivers installed (correctly).
June 30, 2003 at 10:38 am
The SQL Plus session was being run from the Server console, not a client. I have resolved this problem. It turns out that the SQL server was not stoppped and restarted after the Oracle Client was installed (it did not require a reboot) so SQL Agent did not have access to all of the environment settings needed to run Oracle. Thanks to everyone for their comments and help.
Mark
Mark Hottinger
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply