MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Fix SQL Server 2008 Client

with 65 comments

I finally got back to my Microsoft SQL Server 2008 Express installation for a bit of comparison documentation in the sqlcmd.exe (the client tool peer to sqlplus and mysql). After all the energy to install it, I found it didn’t work.

Update for SQL Server 2012 – it’s still broken the same way and this fix works.

When I tried to connect with:

C:\>sqlcmd.exe

It raised the following error:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
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.

A bit of poking around yielded an answer on Chris Rasmussen’s blog and a bit more about .NET pipes on Jesse Johnston’s blog. It appears Microsoft SQL Server 2008 Express edition ships and installs with their client tool effectively shutoff.

The client executable uses a pipe to communicate to the server. You need to change the pipe from this default configuration:

\\.\pipe\MSSQL$SQLEXPRESS\sql\query

To this working version

\\.\pipe\sql\query

Then, you need to enable it and restart the service. Here are the step-by-step instructions, so you don’t have to poke around.

You’ll need to launch the SQL Server Configuration Manager to make these changes. You’ll find it by navigating to Start and then open the Microsoft SQL Server 2008 folder. You’ll then select Configuration Tools and launch SQL Server Configuration Manager, as shown in the screen shot.

startmenu

Launching that provides you the following screen shot:

netpipeconfiguration

Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.

netpipedefault

You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:

netpipefixed

Click the Apply button, and the only remaining step requires you to restart the service. The easiest way (thanks to Griffth) is to click on SQL Server Services in the left dialog, then right click the SQL Server (SQLEXPRESS) service and choose Restart.

sqlrestarteasy1

An alternative would be to open a command line window (cmd.exe) and launch the services console with the following command:

C:\> services.msc

Click on SQL Server (SQLEXPRESS), then click the Restart the services link in the middle column, as shown.

sqlserverservice

Now you should be able to launch the SQL Server 2008 Express command line console. You can get some basic help with the tool by doing the following:

C:\Data>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.
 
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

After you’ve configured this, you can schedule routine operations, like the daily submission process in this external post.

Written by maclochlainn

May 16th, 2009 at 12:26 am

Posted in sql,SQL Server 2008

65 Responses to 'Fix SQL Server 2008 Client'

Subscribe to comments with RSS or TrackBack to 'Fix SQL Server 2008 Client'.

  1. Thank You Sooooooooooooo Very Much!!! You seriously saved my day and my hero for today!! Thank you loads! After going through so many links, yours is actually what helped me out!

    Dee

    27 Apr 13 at 7:35 am

  2. thanks- great simple article! good work!

    Harvy

    24 May 13 at 9:24 am

  3. This is exactly what I was looking for. Thank You!!!

    Timothy

    29 May 13 at 5:50 am

  4. You saved my night!! Thanks

    Stan

    5 Jun 13 at 1:39 am

  5. Thanks champ! This really helped!!

    Jake

    2 Jul 13 at 9:53 pm

  6. Thank you..
    When I tried sqlcmd in command prompt, “sqlcmd is not recognized as an internal or external command..” so I paste the sqlcmd.exe in that location. It’s OK to use sqlcmd.. But the problem is it’s not connected to SQLEXPRESS. It connet to server.. What should I do.. I tried many way.. Please answer me..
    Thank you all, good luck.

    Myat Thet Htwe

    13 Aug 13 at 5:16 am

  7. You’re a Life Saver.

    Congrats 😉

    OSAT

    8 Oct 13 at 8:22 am

  8. MAn you’re my hero!

    ThankU

    15 Oct 13 at 2:55 am

  9. SQLCMD -E -S servername -Q “BACKUP DATABASE databasename TO DISK=”d:\adw.bak””

    rajesh

    17 Oct 13 at 4:21 am

  10. for restoring the database we can use

    SQLCMD -E -S servername -Q “RESTORE DATABASE databasename FROM DISK=”d:\adw.bak””

    rajesh

    17 Oct 13 at 4:22 am

  11. Thanks a lot. It was exactly what i was looking for. 🙂 🙂

    Eshant Sahu

    24 Feb 14 at 3:29 am

  12. Hi, Can I ask a question my sqlcmd script wont let me write to a folder on the desktop of my win XP PC any suggestions.

    The script work on another win xp with no problem!

    Divjoy

    26 Mar 14 at 2:37 pm

  13. Divjoy, It’s probably an issue with privileges. Check the directory privileges.

    maclochlainn

    27 Mar 14 at 12:20 am

  14. please I need you help

    how to change the name pipes from SMO (server management objet)?

    or

    how to change the name pipes from command of TSQL?

    Wilfredo

    29 Apr 14 at 8:46 am

  15. Wilfredo,

    I’m not aware of a way to change them from inside the instance, which means through you use PowerShell. Check this page for information.

    maclochlainn

    30 Apr 14 at 9:15 pm

Leave a Reply