Hello all,

I am having serious issues with SQL 7.0 DTS. I am trying
setup a nightly job that transfers tables from a
production database to a reporting database every night.
I have used SQL authentication, and NT authentication, -->
I am using the Transfer SQL Objects option (between to SQL
servers...even though it is the same server). All my
setting look correct. In fact, the job works if I run it
myself. If the SQL Agent kicks it off, it dies about 75%
with the following error:

-2147220299 (800404B5)

I have an existing DTS job that copies the same production
database to a different reporting database and it works
fine every night. So, I tried saving that DTS job as an
additional file, made the DB destination change for the
second reporting database, and I get the same behavior.

Is there a way to turn on some sort of descriptive
logging??

Thanks

Ryan Hunt

A. Mitchell, I read your response to a previous person's
question. The job is owned by myself (a domain
administrator). I don't recognize the SQLAgentCmdExec
acount so I assume the articles is refferring to the
service account we use to run our SQL agent... If that is
the case, that ID is a local server admin and a system
administrator for SQL.

Unfortunately, my problem appears different. Any
suggestions? Oh, also, we are primarily a SQL 2000 shop.
Therefore, I build DTS jobs directly on the server so I am
using Enterprise Manager for 7.0. Therefore, when I am
running it and when the SQL agent run the DTS job, both
are running directly on the server.

Thanks

Ryan

Hmmmmmmmm

OK

Change the owner of the job to sa. This will cause the job to execute under
the guise of the SQL Server Agent account. Are you a sysadmin in SQL Server
?
If you log onto the server as the Agent account and press "GO" in the
package what happens?
Enable logging in the package
If the Agent is a local server admin (Local Account) then it will not see
off it's own box.
You say you are primarily a 2000 shop but are using 7's EM ?

How many steps in the package ?
What is the whole error ?
What task are you using ? Transfer Objects ? (The Move Databases task
appeared in 2000)





--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ryan Hunt" <ryan.hunt (AT) highwoods (DOT) com> wrote

- I will perform some testing with owner as SA and as the SQL agent service account

-Yes, I am a sysadmin in SQL Server

- I will check on this. Again, the schedule kicks it off fine. It runs through about 75% of the copy and comes back with the error below. If I run it locally, it runs the duration.

- How do you enable logging in the package?

- The agent does not need to leave the server accept through ODBC, where separate accounts are used. In this case, both DB's are on the same server (local).

-Sorry, I should have been more clear. We have both SQL 7 and SQL 2000...although ironically, our SQL 7.0 box does the vast majority of our DTS work. Anyway, all developers, admins, etc. use SQL 2000's EM for functionality, etc. However, it is my understand that SQL 7.0 cannot read packages written in 2000 EM so I make sure that all SQL 7.0 DTS jobs are built on the server console using SQL 7.0 EM (installed on the 7.0 server)

-Essentially just one or two. Other than the DB connections, that package shows up as one step (transfer objects).

-The whole error is:
SQL Server Scheduled Job 'PRD TO 0714' (0x9651349E49F0C345A4958124B2577140) - Status: Failed - Invoked on: 10/9/2003 4:17:00 PM - Message: The job failed. The Job was invoked by Schedule 127 (PRD TO 0714). The last step to run was step 1 (PRD TO 0714).

-Yes, I am using transfer objects.

Thanks in advance for your help.

Ryan Hunt



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

Logging in package = Right click Whitespace | Package properties | Logging
7 can use and manipulate 2000 packages

Service Pack and Version Compatibility Problems
(http://www.sqldts.com/default.aspx?223)

Is the error info from the job step details or just the job ? (Little check
box top right)

Was the package created on 200 and the server is 7 ? If it is then check
for non 7 objects (Functions)

The logging should tell us where the error is though.

have you tried logging in as the SQL Server Agent account and running it
from the server ?

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ryan Hunt" <ryan.hunt (AT) highwoods (DOT) com> wrote

- I will perform some testing with owner as SA and as the SQL agent service
account

-Yes, I am a sysadmin in SQL Server

- I will check on this. Again, the schedule kicks it off fine. It runs
through about 75% of the copy and comes back with the error below. If I run
it locally, it runs the duration.

- How do you enable logging in the package?

- The agent does not need to leave the server accept through ODBC, where
separate accounts are used. In this case, both DB's are on the same server
(local).

-Sorry, I should have been more clear. We have both SQL 7 and SQL
2000...although ironically, our SQL 7.0 box does the vast majority of our
DTS work. Anyway, all developers, admins, etc. use SQL 2000's EM for
functionality, etc. However, it is my understand that SQL 7.0 cannot read
packages written in 2000 EM so I make sure that all SQL 7.0 DTS jobs are
built on the server console using SQL 7.0 EM (installed on the 7.0 server)

-Essentially just one or two. Other than the DB connections, that package
shows up as one step (transfer objects).

-The whole error is:
SQL Server Scheduled Job 'PRD TO 0714'
(0x9651349E49F0C345A4958124B2577140) - Status: Failed - Invoked on:
10/9/2003 4:17:00 PM - Message: The job failed. The Job was invoked by
Schedule 127 (PRD TO 0714). The last step to run was step 1 (PRD TO 0714).

-Yes, I am using transfer objects.

Thanks in advance for your help.

Ryan Hunt



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

Logged in as the SQLAgent service account, if I right click on the job and tell it to run, the job will complete 100% within 40 minutes.

If I let the scheduler kick it off, it is still failing after 35 minutes. Here is the log:

The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step 'Transfer SQL Server Objects' failed.
Error code: 80040428
\Error Help File:sqldts.hlp
Error Help Context ID:700


Package Name: PRD TO 0714
Package Description: DTS package description
Package ID: {FCBBD6CA-804C-4FE4-9CE8-79376DCE3196}
Package Version: {80FB6EFE-9533-4681-97B7-51CCB9D657A5}
Package Execution Lineage: {926DBBA6-B93E-454F-886A-F36E5DEFBB9A}
Executed On: RALSQL02
Executed By: svc_SQLAcct
Execution Started: 10/10/2003 11:26:01 AM
Execution Completed: 10/10/2003 11:59:58 AM

Package Steps execution information:

Step 'Transfer SQL Server Objects' failed

I was unable to find the check mark you were referring to so I would have to say this was a job detail not a step detail.

-The package was created using SQL 7.0

Thanks

Ryan Hunt


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

For the moment if you are using this task to move the whole DB I would use
in preference BACKUP and RESTORE. I cannot think immediately of a reason
why if YOU execute the job as the Agent it works but the scheduler running
as the Agent fails.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ryan Hunt" <ryan.hunt (AT) highwoods (DOT) com> wrote

Logged in as the SQLAgent service account, if I right click on the job and
tell it to run, the job will complete 100% within 40 minutes.

If I let the scheduler kick it off, it is still failing after 35 minutes.
Here is the log:

The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step 'Transfer SQL Server Objects'
failed.
Error code: 80040428
\Error Help File:sqldts.hlp
Error Help Context ID:700


Package Name: PRD TO 0714
Package Description: DTS package description
Package ID: {FCBBD6CA-804C-4FE4-9CE8-79376DCE3196}
Package Version: {80FB6EFE-9533-4681-97B7-51CCB9D657A5}
Package Execution Lineage: {926DBBA6-B93E-454F-886A-F36E5DEFBB9A}
Executed On: RALSQL02
Executed By: svc_SQLAcct
Execution Started: 10/10/2003 11:26:01 AM
Execution Completed: 10/10/2003 11:59:58 AM

Package Steps execution information:

Step 'Transfer SQL Server Objects' failed

I was unable to find the check mark you were referring to so I would have to
say this was a job detail not a step detail.

-The package was created using SQL 7.0

Thanks

Ryan Hunt


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