Proper way to detect if SQLCMD.exe is installed?

Question

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.

Facsimile

You could try to execute sqlcmd.exe -? in a process in your C# app - if it works, then SQLCMD is present - if not, it'll tell you something like "file not found" or "command invalid" or something .... – Livvy

@Livvy yea that is what i was thinking...just not what I thought to be the cleanest for a class library that will an essential component of many applications. – Facsimile

Answer 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.

Hawkins

SQL Server could be installed into a different directory (like on the D: drive or something) - so checking the directory is not a very reliable way... you definitely need to go fetch the Tools directory from the registry first... – Livvy

Be aware that if you're doing this in C# and you use the Registry.LocalMachine.OpenSubKey() method, the x86 location will be returned on machines running 64 bit windows (e.g. C:\Program Files (x86)\...). You'll get a "file not found" in this case because SQL Server doesn't install SQLCMD there. Note this only applies if your application is x86. – Woodhead

Also note, sql2012 tools install into ..Microsoft SQL Server\110\Tools.. (ie not '100') and it doesn't get put into the path if you install the embedded tool via SqlCmdLnUtils.msi. It seems unwise to use a reg key that includes a version-specific number as well. How about HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SqlCmdLnUtils\CurrentVersion ? – Cynar

@PandaWood: Well, that doesn't work, as "SqlCmdLnUtils" is called "SQLLNCLI10" in the 2008 version, and while it does have a version number, it isn't obviously related to "100". So we're reduced to checking all possible places in the registry, ie "110", then "100", and anywhere else you might know about. – Drus

Answer 2

Start cmd.exe and use

where sqlcmd.exe

Rudy

It helped as I was looking for the path to create DB backup scripts. Thanks! – Krissykrista

Answer 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;
    

Extravasate