I am trying to use SQLCMD to remotely detach a database but for some reason it can't find the server. From the workstation, I can connect to the database instance (which is the default instance) using the Host Name through SSMS successfully. The error is:

TCP Provider: No such host is known.
Sqlcmd: Erorr: 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.

So the first thing I did was to make sure that the instance is accessible via SSMS (it is), and that my CMD prompt is Administrator (it is). Then I checked to make sure the instance has TCP enabled (it does) and allows remote connections (it does).

What am I missing? The commend I use, minus the script, is:

SQLCMD -D (database) -S TCP:(hostname) -Q (query)
  • Instead of hostname did you try IP address? Is it a default instance or a named instance? What port is SQL Server running on, and is the SQL Server browser service running on the remote server? – Aaron Bertrand Sep 29 '14 at 16:21
  • Default instance, browser is running, IP address doesn't work (and the server is able to be pinged on the hostname) – Sean Long Sep 29 '14 at 16:46
  • Which copy of sqlcmd are you using? If you're just opening a command prompt and typing sqlcmd, you might not be using the most recent version installed on your machine. – Aaron Bertrand Sep 29 '14 at 16:48
  • It's the one at C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE, so I think that's SQL 2008 R2? I only have that particular version installed locally, not SQL2012 or 2014 (yet). – Sean Long Sep 29 '14 at 16:50
  • You don't need to specify TCP: for the hostname. Simply use -S hostname. Not that it should matter either way. When you connect using SQL Server Management Studio, are you doing that from the same machine as where you are using sqlcmd? – Max Vernon Sep 29 '14 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.