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:
It works fine on my laptop, but it fails and returns below error when running in the build in Visual Studio Online:
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: link.
I hope this will be helpful.
Comments on “Finding the Compatible SQLCMD.exe”