Finding the Compatible SQLCMD.exe

I wrote this post because I hope it can save other people some time. When I ran into this issue this week, I searched in Bing/StackOverflow/etc. and couldn’t find a direct answer for it. So I spent some time to do my own troubleshooting, try different solutions and have figured out a workable one. This post captures the issue and my solution, so that hopefully in the future when other people run into the same issue, they will find this post by searching in Bing/Google.


The Issue

In my unit test’s TestInitialize code, it runs such a sqlcmd.exe command:

sqlcmd.exe -S (LocalDB)\UnitTest -E -d Jobs_DBTNXXVKQ3K6 -i "..\src\SQL Database\Jobs\Tables\Jobs.sql"

It works fine on my laptop, but it fails and returns below error when running in the build in Visual Studio Online:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

That’s because this sqlcmd.exe was v10.0 (SQL Server 2008) and it’s incompatible with LocalDB, which was introduced in SQL Server 2012 (v11.0). Only the sqlcmd from SQL Server 2012 or later works with LocalDB.

The Solution

The solution is to find a later version of sqlcmd.exe on the build host of Visual Studio Online, and pinpoint to it in my TestInitialize code. This and this page listed what’s installed on the build host, but for obvious reasons, in my TestInitialize I must do a search instead of using a hard-coded path.

A little surprise was that to do the file search in the build host in Visual Studio Online, I couldn’t use the DirectoryInfo.GetFiles() method with the SearchOption.AllDirectories parameter. That would throw exception when it gets denied access to some folders and there doesn’t seem to be a way to let DirectoryInfo.GetFiles() just ignore any directory that it get access denied.

So I ended up writing a traversal by myself rather than using DirectoryInfo.GetFiles(). The traversal is kind of time consuming: it takes about 15-30 seconds on my laptop (probably because I’ve installed too many stuffs under the program files folder). So I added a shortcut: first try to look for it at a few known possible places; if found, then the time-consuming traversal can be saved.

Here is the full code for finding the compatible SQLCMD.exe:

/* Only v11.0 and later version sqlcmd.exe is compatible with SQL Server 
 * LocalDB. Unfortunately, at this moment, the default sqlcmd.exe in VSO
 * is v10.0. This method is to find a compatible sqlcmd.exe. It doesn't 
 * have to be the latest.
 */
private static FileInfo FindCompatibleSqlcmd()
{
  /* Try a few possible known places first. If found, it saves time in 
   * doing the full blown search. 
   */
  string[] knownPossiblePlaces = new string[]{
     @"C:\Program Files\Microsoft SQL Server"
     + @"\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE"
    ,@"C:\Program Files\Microsoft SQL Server" 
     + @"\110\Tools\Binn\SQLCMD.EXE"
  };
  foreach (var file in knownPossiblePlaces)
  {
    if (File.Exists(file))
    {
      logger.LogInfo("Got a match in known places: {0}", file);
      return new FileInfo(file);
    }
  }

  /* Now do a full blown search, using code sample from
   * https://msdn.microsoft.com/en-us/library/bb513869.aspx
   */
  FileInfo answer = null;
  string[] paths = new string[]{
    Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles),
    Environment.GetEnvironmentVariable("ProgramW6432"),
    Environment.GetEnvironmentVariable("ProgramFiles")
  };
  foreach (var path in paths.Distinct())
  {
    Stack stack = new Stack();
    stack.Push(path);

    while (stack.Count > 0)
    {
      string currentDir = stack.Pop();
      string[] subDirs;
      try
      {
        subDirs = Directory.GetDirectories(currentDir);
      }
      catch (UnauthorizedAccessException)
      {
        logger.LogInfo("Access denied to folder: {0}" , currentDir);
        continue;
      }
      catch (DirectoryNotFoundException)
      {
        logger.LogInfo("Access denied to folder: {0}" , currentDir);
        continue;
      }

      string[] files = null;
      try
      {
        files = Directory.GetFiles(currentDir);
      }
      catch (UnauthorizedAccessException)
      {
        logger.LogInfo("Access denied to folder: {0}" , currentDir);
        continue;
      }
      catch (DirectoryNotFoundException)
      {
        logger.LogInfo("Directory not found: {0}" , currentDir);
        continue;
      }

      foreach (string file in files)
      {
        try
        {
          FileInfo fi = new FileInfo(file);
          if (fi.Name.Equals("sqlcmd.exe"
                            , StringComparison.OrdinalIgnoreCase))
          {
            logger.LogInfo("Found: {0}, created on {1}"
                           , fi.FullName
                           , fi.CreationTime);
            if (null == answer || answer.CreationTime < fi.CreationTime)
            {
              answer = fi;
              logger.LogInfo("Update answer to: {0}", fi.FullName);
            }
          }
        }
        catch (FileNotFoundException)
        {
          logger.LogInfo("File was just deleted: {0}", file);
          continue;
        }
      }

      foreach (string str in subDirs)
        stack.Push(str);
    }
  }
  logger.LogInfo("FindCompatibleSqlcmd result: {0}" 
         , answer == null ? "null" : answer.FullName);
  return answer;
}

I hope this will be helpful to somebody some day.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s