SQL Server 2000 SP3, Win 2000 Server SP3

I have a DTS package which reads data from an Oracle database.
Installed the Oracle client 8.1.6.0.0 on the server. If I log in using
the account under which the SQL Server Agent runs, I can run SQL*Plus
and tnsping against the Oracle database and can execute the package
and also a job which executes the package. However, when I schedule
the job it fails with the message 'Oracle client and networking
components were not found...'

This has all the hallmarks of a permissions problem, but have no idea
where to look. Can anyone help?

Thanks

Scott Doughty

Who owns the job?
If it is not a member of the sysadmin group then job will fire as the Agent
Proxy 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


"Scott Doughty" <scott.doughty (AT) bigfoot (DOT) com> wrote

Thanks for getting back

The job is owned by my domain account, which is a SQL Server system
admnistrator (to be precise, a domain group of which I am a member has
'System Administrators' checked on the 'Server Roles' tab in
Security/Logins). I am not a Windows administrator. I'm no sure if we
have an Agent Proxy accout set up - when you open up SQL Server
Agent/Properties/Job System the 'Only users with SysAdmin
privileges...' box is unchecked. I've tried running
EXEC master.dbo.xp_sqlagent_proxy_account 'GET'
to check this but get 'Access is denied'

Scott

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

If you are a member of the sysadmin role and the job is owned by you then
the Executor of the job will be the SQL Server Agent Account. If you can
log in as this account on the server itself and execute everything perfectly
happily then I am unsure what else can be wrong. It obviously thinks you
are missing something in the drivers.

Do you have Oracle in the PATH ?

--

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


"Scott Doughty" <scott.doughty (AT) bigfoot (DOT) com> wrote

Alla
Thanks for your help. I've got it to work by ticking the 'Only users with SysAdmin privileges...' box in SQL Server Agent/Properties/Job System and confirming that the proxy account was a local administrator. Just wish I understood why this was required

Scott