A basic question I know, but can somebody tell me how to add On Success,
On Failure and On Completion arrows between tasks? I have done quite a
bit of DTS, but never had to use these before.
Thanks,
Mike
*** Sent via Developersdex http://www.examnotes.net ***Click Task1
CTRL+Click Task2
Right Click
WorkFlow
TaDa...
Suggest having a look here www.sqldts.com
HTH. Ryan
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eV2vA%23MLGHA.3064@.TK2MSFTNGP10.phx.gbl...
>A basic question I know, but can somebody tell me how to add On Success,
> On Failure and On Completion arrows between tasks? I have done quite a
> bit of DTS, but never had to use these before.
>
> Thanks,
> Mike
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
Showing posts with label failure. Show all posts
Showing posts with label failure. Show all posts
Wednesday, March 21, 2012
Sunday, March 11, 2012
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...
>
>
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...
>
>
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...
> > 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,
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...
> 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...
>> > 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.
>>|||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...
> > 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...
> >> > 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.
> >>
> >>
> >>
>
>
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...
> > 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,
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...
> 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...
>> > 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.
>>|||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...
> > 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...
> >> > 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.
> >>
> >>
> >>
>
>
dts package failure when setup as job
HI I have a dts package (SQL 2000) that I can execute from Enterprise manage
r
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 wel
l
as an oracle database on another server. On job failure the event is writte
n
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 write
s
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 th
e
> 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 m
y
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 Oracl
e,
> but plenty of people have successfully used DTS, Linked Servers, etc betwe
en
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>
r
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 wel
l
as an oracle database on another server. On job failure the event is writte
n
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 write
s
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 th
e
> 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 m
y
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 Oracl
e,
> but plenty of people have successfully used DTS, Linked Servers, etc betwe
en
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>
DTS Package failure
I have a DTS package which keeps failing giving me the error message “A connection with the transaction manager was lost". The DTS package is using two different servers as connection and the DTS package has Use Transaction, Rollback transaction on failure and Join transaction if present ticked. I have checked the MSDTC service is running on both servers, the following options on both servers have been checked; Network DTC Access, Allow Inbound and Outbound. Does anybody know what is causing this error message?
Any suggestions much appreciated.
Regards
AmberThis is no longer a problem as we removed Use Transactions on the DTS package and added additional steps on failure to allow rollback.
Any suggestions much appreciated.
Regards
AmberThis is no longer a problem as we removed Use Transactions on the DTS package and added additional steps on failure to allow rollback.
Friday, March 9, 2012
DTS Package - Execute Sql Task
Hello
Could someone please tell me what constitutes a "success" and a "failure"
result when running an "Execute Sql" task within a DTS package.
For instance, within my DTS package, I have an Execute Sql task which simply
runs a stored procedure and returns a scalar value which is the number of
records in a table (ie SELECT COUNT(1) FROM TABLENAME).
Now if that value is below a predermined value (global variable), I want the
Execute Sql task to report "success" and then carry on to execute an ActiveX
task using the "On Success" constraint within the package workflow. If the
value is not below the predetermined value, I wantthe Execute Sql task to
report "failure" and just stop there.
I hope this isn't confusing.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--In your Excecute SQL task, you need to evaluate the scalar value returned
from the stored proc and, if desired, raise an error. This will force the
DTS workflow to the 'failure' route.
Something like this:
if @.var >= @.predeterminedvalue
RAISERROR ('Value was too high',16,1)
Then put an ActiveX script in the failure workflow and make the script one
line:
Main = DTSTaskExecResult_Failure
If you want the package to end after this, then just make sure there are no
other tasks in the workflow after the ActiveX script AND there are no tasks
that run without a predecessor.
"Peter" <spam@.dwstech.com> wrote in message
news:1139320625_5345@.sp6iad.superfeed.net...
> Hello
> Could someone please tell me what constitutes a "success" and a "failure"
> result when running an "Execute Sql" task within a DTS package.
> For instance, within my DTS package, I have an Execute Sql task which
> simply runs a stored procedure and returns a scalar value which is the
> number of records in a table (ie SELECT COUNT(1) FROM TABLENAME).
> Now if that value is below a predermined value (global variable), I want
> the Execute Sql task to report "success" and then carry on to execute an
> ActiveX task using the "On Success" constraint within the package
> workflow. If the value is not below the predetermined value, I wantthe
> Execute Sql task to report "failure" and just stop there.
> I hope this isn't confusing.
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Excellent VC
That is kinda what I was thinking but just wasn't sure if that was an
accepted method.
I could also, of course, put the whole thing in an ActiveX script but then
it gets nasty because I would have to create connection and recordset
objects etc just to return the one scalar (rowcount).
Thanks
Peter
"VC" <me@.here.com> wrote in message
news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
> In your Excecute SQL task, you need to evaluate the scalar value returned
> from the stored proc and, if desired, raise an error. This will force the
> DTS workflow to the 'failure' route.
> Something like this:
> if @.var >= @.predeterminedvalue
> RAISERROR ('Value was too high',16,1)
> Then put an ActiveX script in the failure workflow and make the script one
> line:
> Main = DTSTaskExecResult_Failure
> If you want the package to end after this, then just make sure there are
> no other tasks in the workflow after the ActiveX script AND there are no
> tasks that run without a predecessor.
>
> "Peter" <spam@.dwstech.com> wrote in message
> news:1139320625_5345@.sp6iad.superfeed.net...
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter - Yes, putting the whole thing in an ActiveX script is possible, but I
personally think that is a bad design. I hate the idea of a DTS package
sitting right on the SQL Server running a script that creates an ADO
connection to the SQL Server itself in order to execute a simple SQL. It is
just a lot of unnecessary overhead.
"Peter" <spam@.dwstech.com> wrote in message
news:1139325919_5391@.sp6iad.superfeed.net...
> Excellent VC
> That is kinda what I was thinking but just wasn't sure if that was an
> accepted method.
> I could also, of course, put the whole thing in an ActiveX script but then
> it gets nasty because I would have to create connection and recordset
> objects etc just to return the one scalar (rowcount).
> Thanks
> Peter
> "VC" <me@.here.com> wrote in message
> news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
Could someone please tell me what constitutes a "success" and a "failure"
result when running an "Execute Sql" task within a DTS package.
For instance, within my DTS package, I have an Execute Sql task which simply
runs a stored procedure and returns a scalar value which is the number of
records in a table (ie SELECT COUNT(1) FROM TABLENAME).
Now if that value is below a predermined value (global variable), I want the
Execute Sql task to report "success" and then carry on to execute an ActiveX
task using the "On Success" constraint within the package workflow. If the
value is not below the predetermined value, I wantthe Execute Sql task to
report "failure" and just stop there.
I hope this isn't confusing.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--In your Excecute SQL task, you need to evaluate the scalar value returned
from the stored proc and, if desired, raise an error. This will force the
DTS workflow to the 'failure' route.
Something like this:
if @.var >= @.predeterminedvalue
RAISERROR ('Value was too high',16,1)
Then put an ActiveX script in the failure workflow and make the script one
line:
Main = DTSTaskExecResult_Failure
If you want the package to end after this, then just make sure there are no
other tasks in the workflow after the ActiveX script AND there are no tasks
that run without a predecessor.
"Peter" <spam@.dwstech.com> wrote in message
news:1139320625_5345@.sp6iad.superfeed.net...
> Hello
> Could someone please tell me what constitutes a "success" and a "failure"
> result when running an "Execute Sql" task within a DTS package.
> For instance, within my DTS package, I have an Execute Sql task which
> simply runs a stored procedure and returns a scalar value which is the
> number of records in a table (ie SELECT COUNT(1) FROM TABLENAME).
> Now if that value is below a predermined value (global variable), I want
> the Execute Sql task to report "success" and then carry on to execute an
> ActiveX task using the "On Success" constraint within the package
> workflow. If the value is not below the predetermined value, I wantthe
> Execute Sql task to report "failure" and just stop there.
> I hope this isn't confusing.
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Excellent VC
That is kinda what I was thinking but just wasn't sure if that was an
accepted method.
I could also, of course, put the whole thing in an ActiveX script but then
it gets nasty because I would have to create connection and recordset
objects etc just to return the one scalar (rowcount).
Thanks
Peter
"VC" <me@.here.com> wrote in message
news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
> In your Excecute SQL task, you need to evaluate the scalar value returned
> from the stored proc and, if desired, raise an error. This will force the
> DTS workflow to the 'failure' route.
> Something like this:
> if @.var >= @.predeterminedvalue
> RAISERROR ('Value was too high',16,1)
> Then put an ActiveX script in the failure workflow and make the script one
> line:
> Main = DTSTaskExecResult_Failure
> If you want the package to end after this, then just make sure there are
> no other tasks in the workflow after the ActiveX script AND there are no
> tasks that run without a predecessor.
>
> "Peter" <spam@.dwstech.com> wrote in message
> news:1139320625_5345@.sp6iad.superfeed.net...
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter - Yes, putting the whole thing in an ActiveX script is possible, but I
personally think that is a bad design. I hate the idea of a DTS package
sitting right on the SQL Server running a script that creates an ADO
connection to the SQL Server itself in order to execute a simple SQL. It is
just a lot of unnecessary overhead.
"Peter" <spam@.dwstech.com> wrote in message
news:1139325919_5391@.sp6iad.superfeed.net...
> Excellent VC
> That is kinda what I was thinking but just wasn't sure if that was an
> accepted method.
> I could also, of course, put the whole thing in an ActiveX script but then
> it gets nasty because I would have to create connection and recordset
> objects etc just to return the one scalar (rowcount).
> Thanks
> Peter
> "VC" <me@.here.com> wrote in message
> news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
Tuesday, February 14, 2012
DTS Import Error (Catastrophic Failure).
I am attempting to do an import from an Access Database
which contains tables and Views. Upon running the DTS
import wizard, I am receiving an error "Catastrophic
Failure" and can find no knowledge base articles on. The
file is approximately 3 MegaB in size.
After the import, the table's structure comes but no data
appears. There are fields which have Memo fields as
Datatype.
Expecting ur Help
Thanks, Stan
.If you have datetiem fields in your Access database, consider this:
Access accepts values for datetime fields >= January 1st, 100
MS SQL SERVER accepts values for datatime fields >= January, 1, 1753
Check in your Access database if no datetime filed has a value (due to typing error or so) before January 1, 1753. If it has this is your problem. SQL Server will never imports such date values.
IONUT
which contains tables and Views. Upon running the DTS
import wizard, I am receiving an error "Catastrophic
Failure" and can find no knowledge base articles on. The
file is approximately 3 MegaB in size.
After the import, the table's structure comes but no data
appears. There are fields which have Memo fields as
Datatype.
Expecting ur Help
Thanks, Stan
.If you have datetiem fields in your Access database, consider this:
Access accepts values for datetime fields >= January 1st, 100
MS SQL SERVER accepts values for datatime fields >= January, 1, 1753
Check in your Access database if no datetime filed has a value (due to typing error or so) before January 1, 1753. If it has this is your problem. SQL Server will never imports such date values.
IONUT
Subscribe to:
Posts (Atom)