SQL 2000 SP3 standard on Windows 2000 Adv server.

Package is used to transfer data from Oracle. It could run manually but
failed when scheduled.

Here is the log:

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_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

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.

I checked the path enviroment variables and the oralce path is under system
variable.

Any other idea?

Thanks,

Amsteel -

Not an expert, especially with Oracle ODBC, but I think you are having
problems making the connection. A scheduled package runs under a
different security context... that of the SQL Agent as I recall. And
runs at the SQL server. While manually run package run at your
workstation, under your username. The difference in these contexts is
the key to the problem. Does Oracle use integrated security? Are the
Oracle drivers installed at the server? Stuff like that.

HTH - Best Regards, Lee Gillie - Spokane, WA

"Amsteel" <z2333 (AT) hotmail (DOT) com> wrote

Oracle use "standard" user name and password. I manually run the package
using the console of the server and everything is fine. I did check
"persistant security" in connection properties. The account SQL Agent using
are local admin of the server.

Any suggestion?

Thanks,

"Lee Gillie" <ANTISPAMIFICATION_lee (AT) odp (DOT) com> wrote

When you manually ran the package did you log onto the server console as the
SQL Server Agent service account?

As Lee said, the SQL Server Agent service account is normally the execution
context of the package when scheduled, but only if your SQL Server Agent Job
is owned by a sysadmin, otherwise it uses the Poxy Account, or fails if
there is no Proxy Account.

Have a look at this KB for some more stuff on this topic-
http://support.microsoft.com?id=269074


--
Darren Green
http://www.sqldts.com



"Amsteel" <z2333 (AT) hotmail (DOT) com> wrote

Yes, there is no problem if I log on as the service account and run it
manually. The account is local administrator and belongs to system
administrators role in SQL Server.

Any suggestion?

Thanks very much.


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

I tried different ways to do it. Saving the package as vbs file or dts file,
then manually run it, there is no problem. But if I schedule it, I got the
same error message in the output log file.

Thanks,

"Amsteel" <z2333 (AT) hotmail (DOT) com> wrote

I am coming into this thread late but

the 80004005 error is permission denied or "I do not exist"

You will need the DSN set up so that the SQL Server Agent service account or
the Agent Proxy account can see it.


Have a read of this

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

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Amsteel" <z2333 (AT) hotmail (DOT) com> wrote

Thanks Allan,

I am not sure why do I need a DSN because I didn't use it. The Oralce client
configuration is fine. I can ran it manually on the server under the SQL
Agent account.

I think now the question now is what is the different between manually run
the package under SQL Agent account and schedule it.

Any idea?

Thanks,


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

The job will execute as the Agent account if the owner of the job is in the
sysadmin role. If not it executes as the Agent Proxy Account.
Were you at the server when you logged in as the Agent account ?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Amsteel" <z2333 (AT) hotmail (DOT) com> wrote

Yes, and The setting of "Only the Sysadmin can run the CmdExec and
ActiveScript jobs" are checked.

Thanks,


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