|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:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\EnvironmentThis 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.
Last updated 22 May 2009