MacLochlainns Weblog

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.

Launching that provides you the following screen shot:

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

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

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.

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.

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.