I have created a DTS package that basically copies tables from a
Oracle server to MS SQL server. When I run the package manually using
the Enterprise Manager, it runs file. But the when I schedule the
package to run at a later time, it gives errors listed below.

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_ENV failed
Step Error code: 80004005

I also see the following error in the system log
Application popup: Microsoft ODBC for Oracle : The Oracle(tm) client
and networking components were not found. These components are
supplied by Oracle Corporation and are part of the Oracle Version 7.3
(or greater) client software installation.

You will be unable to use this driver until these components have been
installed.

The version of MDAC that is installed is 2.71.9030.9
The Oracle client version is 8.1

Note that I only get the above errors for scheduled packages. When run
manually, it runs fine without any errors.

Please let me know if you have a fix for the above.

Thanks.

DTS is a client appliction so it runs where the button is pushed. If you have SQL Server tools + Oracle driver on your desktop and
you exxecute a package on ServerX in msdb then the package is running on your box not the server. Permissions, paths and software
are relative to the executing box

http://support.microsoft.com/?kbid=269074

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"khajoor" <rjalisatgi (AT) yahoo (DOT) com> wrote

Hi Allan,

Thanks for the information about the article.

I now know what the problem is. The problem is that the DTS package is
owned by a Windows NT domain account, but the SQL server agent is
started by the Local System account. So when I start the SQL server
agent with the same Windows NT domain account, the scheduled package
runs without any errors.

I was wondering if there is another way to do this. I do not want the
SQL server agent service to be started by a Windows NT domain account
since the password for that account changes every few weeks and I do
not want to logon on the server every few weeks and change the
password for the SQL server agent service.

Any ideas??

-Rohit


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote