Microsoft SQL Server 2005 Express Problems

Some problems I encountered when using SQL Server Express 2005

I was trying to install SQL Server 2005 Express, and got this problem when trying to access the database:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This is caused by two things:

1. By default, SQL Server has remote connections turned off.
2. SQL Server Browser is needed to establish a remote connection, and this is not automatically enabled.

I fixed this by doing this:

1. Go to SQL Server 2005 Surface Area Configuration Tool (which was installed with SQL server)
2. Go to SQLEXPRESS / Database Engine / Remote Connections
3. Make sure "Local & Remote Connections" is selected.
4. Select either TCP/IP, named pipes, or both.
5. Go to My Computer / Manage/ Services and restart the database engine (Sql Server SQLEXPRESS) service. Or maybe even restart the computer.
6. Also, you need to specify the Server name. I used "-S .\SQLEXPRESS" otherwise the bcp program tries to connect to the DEFAULT server, whatever that is.
7. In the Surface Area Configuration Tool, click on the Service under SQL Server Browser, and set the Startup Type to Automatic and make sure the Service Status is Running. Programs other than SQL Server itself cannot discover that your database exists without the SQL Server Browser running.

Accessing SQL Server from the command line / batch processing

To access SQL Server data from the command line:

1. Find the SQLCMD.exe program in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn directory.
2. run "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -S .\SQLEXPRESS -i myscript.txt
3. Put your SQL commands in myscript.txt, thus:
   select * from mydatabase.dbo.mytable
go
You must put the word "go" to make it actually do something.

If you miss off the "-i myscript.txt" then you get to work interactively at the command prompt. Still need to type "go" to make it work.