'Proper way to detect if SQLCMD.exe is installed?

I am creating a class library that takes .SQL files as input (FileInfo) and a connection string. It then attempts to execute the sql file against the connection.

I have decided to support Microsoft's SMO and SQLCMD.exe

In testing, I have noticed on many machines in my environment, that SQLCMD does not come installed by default. My program errors when just attempting to run the process SQLCMD.exe.

What is the proper way to look for it without searching the entire hard drive? Is there a common registry location that specifies if it is installed? Usually when it is installed, I think a PATH location is set.

Many thanks.

c++sharp.netsqlcmd


Solution 1:[1]

I have a 64 machine with 64 bit SQL SERVER (2k8 R2) and my SQLCMD.EXE is in c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE.

It's also in the path.

You could just look for the path directly from the SQL Server Registry location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup\path

Or a variant on this for a different version.

The big issue here is that SQLCMD is part of the client tools, not the SQL Server so I reckon you cannot ask SQL Server to tell you. Unless of course you're running on the server itself.

Solution 2:[2]

Start cmd.exe and use

where sqlcmd.exe

Solution 3:[3]

Here's a solution - using execute process.

It works for all install variants of MS SQL (with 100-170)

 function ExecuteProcess(const FileName, Params: string;
      Folder: string; WaitUntilTerminated, WaitUntilIdle, RunMinimized: Boolean;
      var ErrorCode: Integer): Boolean;
    var
      cmdLine: string;
      WorkingDirP: PChar;
      StartupInfo: TStartupInfo;
      ProcessInfo: TProcessInformation;
    begin
      Result := true;
      cmdLine := '"' + FileName + '" ' + Params;
      if Folder = '' then
        Folder := ExcludeTrailingPathDelimiter(ExtractFilePath(FileName));
      ZeroMemory(@StartupInfo, SizeOf(StartupInfo));
      StartupInfo.cb := SizeOf(StartupInfo);
      if RunMinimized then
      begin
        StartupInfo.dwFlags := STARTF_USESHOWWINDOW;
        StartupInfo.wShowWindow := SW_SHOWMINIMIZED;
      end;
      if Folder <> '' then
        WorkingDirP := PChar(Folder)
      else
        WorkingDirP := nil;
      if not CreateProcess(nil, PChar(cmdLine), nil, nil, false, 0, nil,
        WorkingDirP, StartupInfo, ProcessInfo) then
      begin
        Result := false;
        ErrorCode := GetLastError;
        exit;
      end;
      with ProcessInfo do
      begin
        CloseHandle(hThread); // CHECK - CLOSE HERE? or move line down?
        if WaitUntilIdle then
          WaitForInputIdle(hProcess, INFINITE);
        // CHECK ::WaitUntilTerminated was used in C++ sqlcmd.exe
        if WaitUntilTerminated then
          repeat
            Application.ProcessMessages;
          until MsgWaitForMultipleObjects(1, hProcess, false, INFINITE, QS_ALLINPUT)
            <> WAIT_OBJECT_0 + 1;
        CloseHandle(hProcess);
        // CHECK :: CloseHandle(hThread); was originally placed here in C++ ...
      end;
    
    end;



procedure TestForsqlCmd();
var
  errCode: Integer;
  success: Boolean;
begin
    
      success := ExecuteProcess('sqlcmd.exe', '-?', '', true, true, true, errCode);
    
      if not success then
      begin
        s := 'The application ''sqlcmd.exe'' wasn''t found!' + sLineBreak +
          'The MS SQLEXPRESS utility is missing.' + sLineBreak +
          'Press EXIT when ready.';
        MessageDlg(s, TMsgDlgType.mtError, [mbOk], 0);
        exit;
      end;
end;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Preet Sangha
Solution 2 Wai Ha Lee
Solution 3 juagicre

Related Questions