Recover from hardware upgrade problems

from the Artful SQL Server & Access Tips List

As Martha Stewart might say, hardware upgrades are a good thing 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 is a known problem. Thanks to 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 directly editing the job:
  1. Run Enterprise Manager (EM)
    2. <.i>Expand the Management node, then the SQL Server Agent tab, and finally the Jobs node
  2. Right-click the job that failed and select Properties
  3. Click the Steps tab, and then click Edit
  4. 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.
Return to the Artful SQL Server & Access tips page