DTSRUN won't, um, run.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DTSRUN won't, um, run....

Author

Topic

MuadDBA

628 Posts

Posted - 2002-02-20 : 16:06:34

Hiya folks...I am having a very wierd problem...I have a SQL2K server, and it has a SQL Job that calls a DTSRUN command. However, it fails with the error "Cannot find the file specified."

Wierd, I think, the DTS file is there, and DTSRUN is there, what's up?

So, I edit the job, and put the full path to the DTSRUN executable there:

C:\program files\microsoft sql server\80\tools\binn\DSRUN.EXE

and viola! it works perfectly.

Hmmm, I think...I'll bet something got gunked up diring the install, and it didn't add that folder to the path. So, I add it to the path environment variable in Win2K....nope, no luck, SQL2K still can't find the file without the full path...well, OK, I'll check the Path variable for the specific account that SQL and SQLAgent log in as..yep, it's there...but the job still fails.

Any suggestions (that don't involve a sledgehammer?)

Joe

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 16:32:52

Try to see if SQLAgent is inheriting the System Path.

To determine if sqlagent is inheriting the system path create a sqlagent cmdexec job to capture the system path using the command: path >> c:\path.txt

Then post path.txt

-Chad

MuadDBA

628 Posts

Posted - 2002-02-20 : 17:04:14

OK, here is what I get:

PATH = (null)

So obviously, it isn't picking up the path environment....any cle as to how to set this up?

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 18:40:26

***THIS POST CONTAINS INFO ABOUT EDITING THE REGISTRY. DO SO AT YOUR OWN RISK, AND HAVE A REGISTRY BACKUP, YADA, YADA*****

The system path is stored in the registry under the following key

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

So check the permissions on this key and also check for any other
currentcontrolset for example

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Control\Session
Manager\Environment

If permissions check out okay then view the above keys using both regedit and regedt32. What may occur is that permissions appear to be okay but using regedt32 there are no entries for the environment folder. If you use regedit to view the key you see the entries. If that is the case then use regedt32 to copy the environment folder from another control set,
ex.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Control\Session
Manager\Environment
and restore to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session
Manager\Environment .

So to copy the key using regedt32, highlight the key and choose Registry -> Save Key . to Import highlight the destination key and choose Registry -> Restore key.

- restart sqlagent

- run dts pkg again and make sure path to dtsrun.exe is not included in the job.

HTH
-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources