When you manually execute a package, it executes from your
PC and under your security context. When you schedule a
package, it executes from the server and under the security
context of the SQL Agent service account (if the job is
owned by an admin).
When you schedule the job and get the error that the
networking components were not found, this indicates that
you haven't installed and configured the Oracle client on
the server. You can run it manually as you have the Oracle
client installed on your PC.
-Sue
On Tue, 30 Mar 2004 06:46:11 -0800, "Holly C"
[quoted text, click to view] <anonymous@discussions.microsoft.com> wrote:
>Through a simple DTS package, I'm copying 5 tables from an Oracle DB to a SQL DB,
>I do have Oracle Client 9i installed on the SQL Server, and I established a DSN for the Oracle DB (ODBC).
>In the DTS package however, if I schedule this to run as a job, (also fails running manually) this is the error:
>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... Process Exit Code 5. The step failed.
>If I change the drivers in my DTS package to OLE DB, it runs manually, but still will not run as a job. I receive this error- Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client softwa... Process Exit Code 5. The step failed.
>I also edited the step in the schedule for the command line, and used dtsrun utility...this is what I have in there...
>dtsrun /Smyservername /E /Nmytablename /M
>Is this valid?
>Also, I have confirmed the SQL Server Agent is a domain user.
>Does anyone have any tips for me? I'm really at a loss. I need to schedule this to run. Currently I'm just leaving it at the OLE DB drivers and running it manually.
>Thanks in advance for any insight! :)
Yes...I've seen it make a difference when the Oracle client
is installed under different accounts. I'd try installing
logged in under the service account. You should probably
also use the tnsping utility on the server to test the
connectivity to Oracle from the server. Again, I'd do this
logged in under the service account.
The software may have been installed but if it's not
configured (tnsnames, etc) then it won't do much good in
that case. If it's installed but not configured, you won't
be able to ping the oracle instance with tnsping.
-Sue
]
On Wed, 31 Mar 2004 06:46:12 -0800, "Holly C"
[quoted text, click to view] <anonymous@discussions.microsoft.com> wrote:
>Sue-
>I do think Oracle Client 9i is install on this machine though. I didn't do the install of this piece.
>The person who did installed under a local admin account he had.
>I know this group is for Microsoft DTS, but do you know should the Oracle Client 9i be installed
>logged in as the same account as the SQL Server Agent Service? Would this make a difference?
>Thanks for all your help,
>Holly C
Don't see what you're looking for? Try a search.
