SQLCMD.EXE on SQL 2008 doesn’t work – HResult 0×2, Level 16, State 1
I’ve built one of my test servers with SQL Server Express 2008 w/ Advanced Services. On a whole load of our servers I use SQL Express for local installations and SQLCMD.EXE works well as a good way to run scheduled backups. It’s done this way because SQL Express doesn’t support SSIS (SQL Server Integration Services), the component required for scheduled tasks. I wrote an article about this a while back called SQL Server 2005 Express Scheduled Backup – How? With SQL 2008, however, that SQLCMD.EXE didn’t work for me out of the box. Here’s how I fixed it …
The instance in question has the default name of SQLEXPRESS. Obviously this means that to connect to the server you need to use SERVER\SQLEXPRESS – that works fine from Management Studio and from the Java application installed on this particular server. You can read about the problems I had with this installation in my article called Apache Tomcat 6 – .WAR won’t deploy.
When using SQLCMD.EXE from the command line the full error message looks like this:
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.
Some Googling suggested that this problem could be caused by one of 2 things. Firstly, that the SQL Browser service isn’t running – on my server it was running. Secondly, named pipes aren’t enabled in the SQL Server Configuration Manager … on mine they are. Hmmm.
I’ve had problems in the past with named instances of SQL Server so I had a look at the properties of the named pipes configuration and, sure enough, it said \\.\pipe\MSSQL$SQLEXPRESS\sql\query. That *looks* ok, right? Yes but it’s the cause of the problem.
I changed the named pipe to the following.
Voila! SQLCMD.EXE now works from the command line meaning my scheduled backups now work too. Not being a full-time DBA I’d put this down to a bit of a fluke on my part although I then found an article by Jesse Johnston that confirmed the same steps worked in their situation too.
Related posts:
- How to perform an unattended installation of SQL Server 2008 Express A while ago I wrote an article called “How to...
- Disable Internet Explorer Enhanced Security Configuration (IEESC) on Windows 2008 The default configuration for Windows Server 2008 still has Internet...
- SQL Server 2005 Express Scheduled Backup – How? A lot of my articles make use of or mention...
- SQL Server 2005 Express Unattended Install – Further Info Recently I posted an article about how to perform an...
- How to perform an unattended installation of SQL Server 2005 Express To continue on with the theme of unattended installation-related posts...
6 Responses to “SQLCMD.EXE on SQL 2008 doesn’t work – HResult 0×2, Level 16, State 1”
Leave a Reply
@DatabaseDave: Haha yeah fun is one way of describing it!
This worked great for me. Thanks!
Solved the issue for me. Thanks!
YOU ARE THE BEST!!!! THANKS!!!!!!
You’re great. You’re my hero. 3 days I search the solution and with just your simple solution, everything is working well right now. Just wanna crying (coz really happy) yipiee..yipiee \(@_@)/