SQL Server Connection Strings - dot(".") or "(local)" or "(localdb)"

database sql-server-2008 sql-server-2008-r2 sql-server-2012 connection-string
Owen picture Owen · Nov 26, 2013 · Viewed 27.6k times · Source

I've recently had to install SQL Server and restore a database to 2 laptops, the first took me a couple of days to figure out, the second I'm still struggling on.

On both I was getting this error here:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'm still getting it on the second.

I did a lot of research and followed all the steps listed on StackOverflow and other sites such as ensuring SQL Server has TCP and Named Pipes running, ensuring that SQL Server was allowing remote connections, and all other troubleshooting steps I could find.

In the end I discovered that I was just entering the connection string wrong on the first laptop. I was trying to use (localdb)\MSSQLSERVER2012 (or something like that) as it says to do on so many sites. It started working when I used .\MSSQLSERVER2012. I thought they would all point to the same place but obviously not.

I really want to avoid this problem again, and figure out how to get my second laptop set up. How do I know if I should be using .\SQLSERVER2012, (local)\SQLSERVER2012, (localdb)\SQLSERVER2012, etc? Is there a simple way of finding this out using a command line tool like SqlLocalDb? And how is this set up in the first place?

Answer

marc_s picture marc_s · Nov 26, 2013

. and (local) and YourMachineName are all equivalent, referring to your own machine.

(LocalDB)\instance is SQL Server 2012 Express only.

The other parts are depending on how you install - if you install with an instance name - then you need to spell that instance name out (SQL Server Express by default uses the SQLEXPRESS instance name, while other editions of SQL Server will try to use the default instance without any special name).

So for a "normal" SQL Server installed with all default options on your local machine, use

.    or   (local)     or          YourMachineName

For SQL Server Express installed with all the default settings, use

.\SQLEXPRESS    or   (local)\SQLEXPRESS     or          YourMachineName\SQLEXPRESS

If you look at the SQL Server Configuration Manager (launch it from the start menu), you'll see:

enter image description here

If the SQL Server entry reads (MSSQLSERVER) then that's that default instance (without any name) - otherwise you'd see the instance name in brackets

Related questions

What is the syntax meaning of RAISERROR()

I just created a Instead After Trigger whose syntax is given below: Create trigger tgrInsteadTrigger on copytableto Instead of Insert as Declare @store_name varchar(30); declare @sales int; declare @date datetime; select @store_name = i.store_name from inserted i …

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
how to schedule a job for sql query to run daily?

I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.

Read More