This came up as a twist in tale for a friend who called to ask what could be wrong with his SQL Server since he’s not able to connect to the default instance using SQLCMD though everything is working fine and he’s able to connect using SSMS. As it turns out, he was on a Cluster.
In case of a cluster, even for the default instance, the SQL Instance name is different from the physical server name. Therefore we need to specify the SQL instance name even for default instance, just like we do for a named instance. For named instance we have to specify the instance name anyways in a standalone or a cluster.
Another small thing some people miss, at least in their first attempt, is that the the variables/switches in SQLCMD are case sensitive. Once they get error they correct it.
Here is my cluster. Please click on images to get a larger or clearer view.
Lets see SQLCMD connectivity error.
Another error with Server switch in small case.
Here is successful connection with Server switch for a default instance
and to a Named Instance. Note that we need to use Server (Name) switch for both default and named instances.
Help with SQLCMD.