As Martha Stewart might say, hardware upgrades are a good
thing—that is, until stuff that used to work just fine suddenly breaks for
no apparent reason. In moments like
this, you invariably end up looking bad, and must drop everything else to find
and fix the problems immediately.

Case in point: A colleague upgraded his server recently,
only to discover that almost all of his scheduled DTS packages broke, with an
error message similar to this:

Error:

Executed as User (xxxx). The process could not be created for step 1 of job
0x..... (reason: the system cannot find the file specified).

The step failed

(The exact error message you receive will differ depending
on which service packs you have installed.)

My colleague immediately recreated both the DTS job and the
SQL job, with the strange result that he could execute the DTS job manually but
not as a scheduled job.

It turns out that this is a known problem. Due to the
virtuoso programming at Microsoft, it seems that the existing paths you have in
a given installation are not stored, recorded, and replaced after updating your
system with a service pack. In fact, it amounts to little more than a stack, a
push, and a pop.

There are several solutions to this problem. The first
solution involves direct editing of the job in point:

  1. Run
    Enterprise Manager (EM).
  2. Expand
    the Management node, then the SQL Server Agent tab, and finally the Jobs
    node.
  3. Right-click
    the job that failed and select Properties.
  4. Click
    the Steps tab, and then click Edit.
  5. Edit
    the path to DTSRun.exe, specifying the full path to the executable.

Another way to do this, which you might find more useful in
the long term because it doesn’t require editing every failed package, is to go
into the registry and perform some edits. Before doing so, ensure that the
startup account for both the SQL Server and SQL Server Agent are part of the
local Administrators group. If not, add one or both. Now you can edit the
registry. You need to verify the value of the following entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

This variable should house the path to the DTSRun executable. In addition, check the value of the
environment variable path. You may need to edit this, removing nonsense
characters and erroneous paths.

I recommend regular inspection of the path variable,
particularly if you frequently install new software, especially trial versions.
Trial software does not perform perfect cleanup, and you can end up with
non-existent paths in your path variable—this won’t cause outright damage, but
it will waste your valuable time. Since I frequently install new software (to
test or review), I inspect my path variable every week or so.

For optimal performance, entries that look like %SystemRoot%\System32;%SystemRoot%; should be placed at the beginning of the path
variable. Verify also that …\MICROSOFT SQL SERVER\80\TOOLS\BINN is present in
the path.

If your system has more than one instance of SQL installed
on it, then you should search for all occurrences of DTSRun.exe. If multiple
occurrences exist, you might have problems.

In a similar vein, if you are running instances of SQL 2000
and SQL 7.0 on the same machine, ensure that the path points to the SQL 2000
instance(s) before the SQL 7.0 instances.

I recommend that you save your registry and all your path
variables before doing an update, and then compare them to the new settings
after your update. This forethought just might help you avoid getting egg on
your face after an upgrade.

Note: Editing the registry is risky, so make sure
you have a verified backup before making any changes.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!