SQLCMD was not found when running Process from ASP .NET

Question

I have an ASP .NET application that starts sqlcmd.exe as a Process. In two of our three test environments, we have no problems. However, on the third machine, even though sqlcmd.exe was installed along with client connectivity tools, Process.exe cannot find sqlcmd.exe. The error shown is:

Error running process: System.ComponentModel.Win32Exception (0x80004005): The system cannot find the file specified
       at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
       at L3_CNM.Utilities.Common.SqlcmdHelper.RunRemoteScript(String ServerAddress, String datbaseName, String filePath, String outputFilePath

I am at a loss for why this happens. The differences between the case when everything is fine as opposed to when it fails are:

1) When it works, there is a full SQL server installation. When it fails, we are only installing sqlcmd as a downloaded installation package which points to a SQL server that resides elsewhere.

2) When it works, the application is running from the same disk volume as Windows installation. On the machine that fails, the application is installed on another volume from the Windows installation.

Other key points - the PATH variable shows the location to sqlcmd.exe, the user that is the application pool identity is a part of the local system administrators, when running sqlcmd.exe through command prompt the results are expected.

Any help would be highly appreciated.

Thanks

EDIT: Adding code:

try
{
    Process process = new Process();
    process.StartInfo.FileName = "sqlcmd.exe";
    Logging.Instance.Log(Logging.Levels.Message, "Process start arguments: " + process.StartInfo.Arguments);
    process.StartInfo.UseShellExecute = false;
    process.StartInfo.RedirectStandardOutput = true;
    process.StartInfo.RedirectStandardError = true;
    process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
    process.StartInfo.CreateNoWindow = true;
    if (process.Start())
        Logging.Instance.Log(Logging.Levels.Message, "Process successfully started");
    else
    {
        Logging.Instance.Log(Logging.Levels.Message, "Unable to start process");
    }
    string output = process.StandardOutput.ReadToEnd();
    output += process.StandardError.ReadToEnd();
    process.WaitForExit();
    Logging.Instance.Log(Logging.Levels.Message, "Process exited with output: " + output);
    return output;
}
catch (Exception e)
{
    Logging.Instance.Log(Logging.Levels.Error, "Error running process: " + e.ToString());
    return e.ToString();
}

we are at a loss too without seeing your code how can anyone accurately answer this.. this can be for a number of reasons.. maybe one of the non working environments is not setup exactly like the 2 working environments.. kind of like shooting in the dark and expecting to hit the bulls eye everytime.. please provide more meaningful information or code.. can you show how you are pointing to the other sql servers..? — MethodMan, Dec 03 '15 at 17:02
@MethodMan, I do not care as of now on whether or not the SQL connection succeeds, I am only trying to get to a point where SQLCMD executes. I have tried to provide as much information as I thought would be required, but I have updated the original question with code that is used to execute SQLCMD. — Rishi, Dec 03 '15 at 17:26
your problem is here `process.StartInfo.FileName = "sqlcmd.exe";` perhaps you could add an entry to the .config file of where the `sqlcmd.exe` is located.. it's obvious that there is something incorrect with your `Environment Variables` why don't you copy past the environment variables from the working machine and from the non working machine into a text editor and compare the paths — MethodMan, Dec 03 '15 at 17:31
also the machine that's not working have you tried having them do a simple `Reboot` after the environment variables have been added ? just curious — MethodMan, Dec 03 '15 at 17:36
I understand that having the full path to SQLCMD is going to solve the problem, but that would mean a new code build which is not desirable. I am trying to limit any changes to code at this point, since we are late in the game. A reboot was never performed, I will try re-installing SQLCMD and then rebooting Windows and see where we stand. Thanks! — Rishi, Dec 03 '15 at 18:12

score 0 · Answer 1 · answered Dec 03 '15 at 17:03

I would guess it's about the PATH environment variable.

Couple of tests! One, open a command window and type

WHERE SQLCMD

This will print out anywhere SQLCMD can be seen. If you get nothing, you'll need to update your path. If you get something, it may be a user environment variable, not the system environment variable, which is what ASP.NET would use.

Can you check that the SYSTEM version of this variable contains the right folder? On win8 and above, open start menu and type "edit the system environment variables". Then click "environment variables" and make sure, under System Variables, that PATH contains the folder returned by WHERE SQLCMD If not, put it in, separating from other folders with a semicolon.

I have manually looked to see where the sqlcmd.exe is located and verified that folder to be in the PATH system variable, I will not see what WHERE SQLCMD returns and verify that too. Thanks! — Rishi, Dec 03 '15 at 17:24

score 0 · Accepted Answer · answered Dec 03 '15 at 21:13

0

A simple Reboot. God I hate Windows sometimes.

Thanks @Methodman for the suggestion.

answered Dec 03 '15 at 21:13

Rishi

321
2
6
17

SQLCMD was not found when running Process from ASP .NET

2 Answers2