dts package failure when setup as job

HI I have a dts package (SQL 2000) that I can execute from Enterprise manager
opened on a client machine connected to a server. When I schedule it as a
job though Enterprise manager on the client machine connected to the server
the job fails. The package connects to an SQL database on the server as well
as an oracle database on another server. On job failure the event is written
to the windows event log on the server. The error message is Source SQL
Server agent,
status failed, the job failed.
I am thinking it may have to do with credentials, any ideas how to
troubleshoot this?
thanks.
Paul G
Software engineer.
Paul,
Yes, when you run it from Enterprise Manager, you are running under your
credentials. A scheduled job has different credentials.
When you schedule the DTS package from SQL Agent, it will run either as the
server account or as the account defined in xp_sqlagent_proxy_account for
your server. The SQL Server 2000 Books Online article on
xp_sqlagent_proxy_account has a fairly clear explanation.
Once you determine which credentials your job is using, then you can have
appropriate access granted to that account.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> HI I have a dts package (SQL 2000) that I can execute from Enterprise
> manager
> opened on a client machine connected to a server. When I schedule it as a
> job though Enterprise manager on the client machine connected to the
> server
> the job fails. The package connects to an SQL database on the server as
> well
> as an oracle database on another server. On job failure the event is
> written
> to the windows event log on the server. The error message is Source SQL
> Server agent,
> status failed, the job failed.
> I am thinking it may have to do with credentials, any ideas how to
> troubleshoot this?
> thanks.
> --
> Paul G
> Software engineer.
|||ok thanks for the information. Another problem is part of the package writes
to an Oracle database but the oracle client software is not installed on the
server.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Yes, when you run it from Enterprise Manager, you are running under your
> credentials. A scheduled job has different credentials.
> When you schedule the DTS package from SQL Agent, it will run either as the
> server account or as the account defined in xp_sqlagent_proxy_account for
> your server. The SQL Server 2000 Books Online article on
> xp_sqlagent_proxy_account has a fairly clear explanation.
> Once you determine which credentials your job is using, then you can have
> appropriate access granted to that account.
> RLF
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
>
>
|||Paul,
So that is the other part. When you run a DTS package from Enterprise
Manager in runs on the same machine that Enterprise Manager is running on.
So, if you are running EM on your desktop, that is where DTS runs as well.
Naturally if you have the Oracle client software on your desktop, it will
work for you.
If that is the case, then you will need to evaluate putting the Oracle
client software on the SQL Server. I don't have any experience with Oracle,
but plenty of people have successfully used DTS, Linked Servers, etc between
SQL Server and Oracle Server.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Another problem is part of the package
> writes
> to an Oracle database but the oracle client software is not installed on
> the
> server.
> --
> Paul G
> Software engineer.
>
> "Russell Fields" wrote:
|||thanks for the additional information. I was wondering why it worked from my
client machine, but I do have the Oracle client software installed on it. I
had it on the server before but we ran into an issue with Appachi effecting
IIS so someone removed all of the Oracle software.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> So that is the other part. When you run a DTS package from Enterprise
> Manager in runs on the same machine that Enterprise Manager is running on.
> So, if you are running EM on your desktop, that is where DTS runs as well.
> Naturally if you have the Oracle client software on your desktop, it will
> work for you.
> If that is the case, then you will need to evaluate putting the Oracle
> client software on the SQL Server. I don't have any experience with Oracle,
> but plenty of people have successfully used DTS, Linked Servers, etc between
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>