PRB: SQL Directory Is Not in the Service Path Until a Restart

• Microsoft SQL Server versions 6.0 and 6.5

SYMPTOMS

After performing a new installation of SQL Server or an upgrade that moves files to a new directory, attempts to perform operations within SQL Server that depend on the SQL Server program directory being in the path will fail until Windows NT is restarted. After Windows NT is restarted, the SQL Server directory will appear in the path.

The following is an example operation that may show this problem:

   xp_cmdshell 'bcp pubs.dbo.authors out authors.dat -T -c'

   The name specified is not recognized as an internal or external command,
   operable program or batch file.

WORKAROUND

To work around this problem until Windows NT is restarted, start SQL Server outside of the Service Control Manager in a new command prompt window, by using the "-c" parameter.

MORE INFORMATION

If you include a directory in the path, you can run a utility, such as Bcp.exe, without being located in that directory at the command prompt. The operating system searches all directories in the path to find the program. If the operating system cannot find the program, the operation fails and you receive the error message.

During the setup process, the SQL Server program directory is added to the PATH system environment variable, and a message is broadcast to all top- level windows indicating that the path has changed. Because all SQL Server program files are located in the SQL Server directory and do not depend on the PATH environment variable, a user can connect to and run most queries without any problem. However, extended stored procedures, which may perform functions that depend on the path, may fail as described above.

Because the Windows NT Service Control Manager (Services.exe) does not have a window, it does not receive the message broadcast by setup; it only becomes aware of the path change when it is restarted by Windows NT. The SQL Server service process inherits the environment of the spawning process (Service Control Manager), and thus also does not reflect the inclusion of the SQL Server directory in the path until that time.

Also note that any command prompt windows opened before setup starts will also fail to reflect the path change, but any new command prompt windows will.