5

I have a freshly installed SQL Server 2008 R2 Express. I'm trying to run SQLCMD locally (please do take note locally) but I am receiving the error:

Named Pipes Provider: Could not open a connection to SQL Server [2]
Sqlcmd: Error: Microsoft SQL Serve 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 informations see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0: Login timeout expired.

Why can't I make it work? I already tried everything.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Xian Garcia
  • 51
  • 1
  • 1
  • 5
  • 1
    Saying you tried everything is not that helpful. We need to know some specifics like: Is it a default instance of SQL Server? What does your connection string look like? etc. Your main problem is that you're not locating the server in the first place... – Paul Sasik Feb 11 '13 at 15:33
  • server instance is SQLEXPRESS. I configured TCPIP. I checked the service is running. I started SQL BRowser. still the SQLCMD doesnt work. – Xian Garcia Feb 11 '13 at 15:37
  • What does your connection string look like? – Paul Sasik Feb 11 '13 at 15:40
  • Where can i find the connection string? Im only using SQLCMD at this point. SSMS can actually connect to the sql service but the SQLCMD doesn't. Thanks for the reply. I am simply opening "cmd" and running SQLCMD -e -s SERVERNAME(which is local)\INSTANCENAME – Xian Garcia Feb 11 '13 at 15:41
  • 1
    Ok then. What does your SQLCMD command line look like? It needs to have connection parameters... – Paul Sasik Feb 11 '13 at 15:42
  • its SQLCMD -e -s SERVERNAME(which is local)\INSTANCENAME. Actually even if I try just "SQLCMD" it doesn't work but on my other pc, it is working – Xian Garcia Feb 11 '13 at 15:43
  • For your -s parameter try `.\SQLEXPRESS` or `(localhost)\SQLEXPRESS` or `127.0.0.1\SQLEXPRESS` – Paul Sasik Feb 11 '13 at 15:47
  • Still the same, I also tried that earlier. I Appreciate your help so much. – Xian Garcia Feb 11 '13 at 15:50
  • Did you try all three iterations for connecting? – Paul Sasik Feb 11 '13 at 15:52
  • Yes, I tried the three. :( Sadly its really not working – Xian Garcia Feb 11 '13 at 15:53
  • Take a look at this article very closely. It may help: http://msdn.microsoft.com/en-us/library/ms188247.aspx – Paul Sasik Feb 11 '13 at 15:55
  • I was able to successfully connect using sqlcmd -S tcp:127.0.0.1,1691. Now I'll try to connect remotely, what do I do to it? – Xian Garcia Feb 11 '13 at 16:05
  • Just replace the 127.0.0.1 (the loopback address) with the computer's real IP address. You can get the IP by running ipconfig. You should also jsut be able to use the computer name if you DNS is working properly. – Paul Sasik Feb 11 '13 at 16:06
  • And how do i translate this into my application and SSMS (to connect to the server). I apologize as I am new to setting up dbs but I have been developing applications for several years – Xian Garcia Feb 11 '13 at 16:07
  • Like this: http://www.connectionstrings.com/ – Paul Sasik Feb 11 '13 at 16:08
  • Thanks for that, can I also test it using SSMS? – Xian Garcia Feb 11 '13 at 16:14
  • So It worked on my other pc but didnt again on another pc – Xian Garcia Feb 11 '13 at 16:57
  • now its not working again on the same pc that it worked earlier. i have no idea what's happening.. :( – Xian Garcia Feb 11 '13 at 17:28
  • You need to talk to your IT/network people about DNS and protocols and stuff. There must be something changing, software doesn't spoil. – Paul Sasik Feb 11 '13 at 19:21

6 Answers6

6

I ran into the same problem. I used -S, instead of -s, and removed the "" before and after the servername\instance. Everything ran great after that.

Mike Arney
  • 63
  • 1
  • 5
4

Maybe a little late on the draw here but for future readers with the issue: Try running Command Prompt as Administrator.

Conor Macarthur
  • 41
  • 2
3

In the named pipes properties just change the path to \\.\pipe\sql\query instead of default path

Samsquanch
  • 8,866
  • 12
  • 50
  • 89
Mike.Nettle
  • 31
  • 1
2

I had this same thing happen to me for a client trying to setup a new SQL-Server box.

Open your SQL-Server configuration manager on the machine where SQL is actually INSTALLED.

Look to the SQL Native Client 10.0 Configuration. Under that is Client Protocols. You probably have TCP/IP and Named Pipes DISABLED. You need to ENABLE them.

Sample here

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • In my case I had to do this under the SQL Server Network Configuration tree rather, plus changing the path to `\\.\pipe\sql\query` as mentioned elsewhere – Savage Mar 31 '20 at 15:19
0

This seems to happen too when the server or the instance can't be found.

It will eventually close with the reason (the first part of the error may be what misguided you):

Sqlcmd: Error: ... : Login timeout expired.

Sqlcmd: Error: ... : 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.

So:

  • Check the server name is correct;
  • Check the instance name is correct (and if you didn't mean to use the default instance, try remove the instance name completely);
  • Check if the server can be reached using ping, etc.
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
0

I had the same issue, the reason is because sqlcmd when typed alone without the servername, tries to connect to the default server. If you have named instance then it cannot connect to it without specifying the instance name. Work around is to make the named instance as default by changing the port as shown in this link: SQL Server, convert a named instance to default instance?

Community
  • 1
  • 1
Kavitha
  • 1