I just had to upgrade my version of EM because my hosting company moved my
SQL 7 db to a SQL 2000 server. Now none of my scheduled jobs work, neither
local or the ones that connect to the remote db.

The local db is still SQL 7, nothing has been altered there - tell me if
that is a problem. Do I also have to install SQL 2000 server on this
machine?
The DTS packages run fine from EM.
The scheduled (in SQL Agent) jobs of the DTS packs report success but really
aren't running (elapsed times of 0-1 seconds)
The machine logon, the sql logon, the SQL Agent logins are all the same.
The owner of the DTS package and the SQL Agent job are all the same as the
logons.

I have tried recreating the DTS packages and recreating the scheduled jobs
but the new jobs won't run either.

These jobs worked fine until the EM upgrade no other changes. Any
suggestions?

Thanks in advance for your time.

Kevin Vogler

Have the packages fail on first error.
Have the packages log their execution to text file
Have you applied service packs to 2000
With that kind of runtime I would expect them to have failed
--

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


"Kevin Vogler" <kevin (AT) voglerandassociates (DOT) com> wrote

The packages are set to fail on first
Log file is blank

Maybe this is the main issue. I don't have SQL 2000 installed on the local
machine, only the client tools. I'm running SQL 7 on the local machine
with the jobs. I only installed the 2000 ver of EM to connect to a remote
hosted db that was switched from 7 to 2000. Now I can connect, run DTS
packages from the designer but can't run any jobs scheduled from the
designer. Previously all of these jobs ran fine. Now I can't even run
scheduled dts jobs that only connect to an Access db on another server.
These jobs still run fine in the dts designer and nothing else has been
altered on either machine.

Do I have to install SQL 2000 to get the Agent jobs to run? If so, do I
have to upgrade the db also or just install the server?

Thanks for your help
Kevin Vogler


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

I finally got the jobs to run if I changed the owner of the dts packages to
the admin account and also set the job to run under 'self' which is
different from the Agent account. However, one of the jobs that connects to
another server and an Access mdb file still won't work (reports success
though). My understanding is that the Agent account has to have permissions
on the other server which it still does plus this worked before. I'm really
confused on that one. Any suggestions?

Thanks in advance
Kevin Vogler

Correct. The Agent account, if the job runs as a user in the sysadmin role,
or the Agent Proxy Account, if the job runs as someone not in the sysadmin
role, must have permissions to go wherever you are directing them.

--

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


"Kevin Vogler" <kvogler (AT) voglerandassociates (DOT) com> wrote

Thanks for the response.

That said, the Agent proxy account has permissions on the other server. In
fact nothing has changed but now the job won't run. Any suggestions for
troubleshooting?

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

Can you remind me please of any errors you get

--

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


"Kevin Vogler" <kvogler (AT) voglerandassociates (DOT) com> wrote

No errors. Job log indicates success.

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

I have seen this type of problem before where no errors were returned and
nothing happened (in fact it should have failed because I made it so that it
would)

If you log in as the SQL Server Agent account and execute the package
manually what happens


--

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


"Kevin Vogler" <kvogler (AT) voglerandassociates (DOT) com> wrote

I have set all the logins - server, agent, sqlserver, dts package owner the
same. The login also has appropriate rights on the other server with the
Access db

The only change has been installing EM 2000

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