[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL SERVER 2012 LocalDB ODBC Connection

Posted on 2014-03-22
13
Medium Priority
?
3,815 Views
Last Modified: 2014-04-09
I am running WINDOWS 7 ULIMATE SP1 32-BIT with .NET FRAMEWORK 4.5.1.
I have downloaded and installed SQL SERVER 2012 LocalDB and Microsoft ODBC Driver 11 for SQL SERVER to-day onto this computer and rebooted.
The default LocalDB instance v11.0 is running - checked with SqlLocalDb command.
I have a working database created and processed by SQL SERVER 2012 MANAGEMENT STUDIO and have copied the .mdf and.ldf files into C:Temp.
I wish to create an ODBC System DSN entry for this database.

I go through the wizard, specifying SQL SERVER instance v11.0 which is in my list of servers and get the final screen:

Microsoft ODBC Driver for SQL Server Version 11.00.2270

Data Source Name: A2ZTEST
Data Source Description: a2z
Server: v11.0
Use Integrated Security: Yes
Database: a2ztest
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Attach Database Filename: C:\Temp\a2ztest2.mdf

click TEST DATASOURCE and get this error screen:

Microsoft ODBC Driver for SQL Server Version 11.00.2270

Running connectivity tests...

Attempting connection
[Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
[Microsoft][ODBC Driver 11 for SQL Server]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.

TESTS FAILED!

The LocalDB SQL SERVER instance is not found for some reason despite the fact that it is in the list of SQL SERVER instances presented in the wizard.

How do I set up an ODBC entry for this LocalDB database - where am I going wrong and what else do I need to do ?
0
Comment
Question by:RichardTatlow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39947596
Try using "localhost", "127.0.0.1", or "." for the servername.

Note that if it's not the default instance it should be localhost\NamedInstance.

Also did you configure named pipes to be enabled?
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39947654
Both the SQL Server and the SQL Native Client must have the same communications protocols enabled.  This is what it looks like for an older version.  The SQL Native client in that list has the same options.
SQL Connections
0
 

Author Comment

by:RichardTatlow
ID: 39947804
Thanks for the suggestions. Unfortunately there is no communications protocols option in the ODBC wizard for 'ODBC Driver 11 for SQL SERVER' and the standalone LocalDB installation does not provide any configuration or administration utilities to set a communications protocol.

When I start the localDB instance it returns a named pipe so I wonder if that is the problem - it needs a named pipe but there is no way of setting one in the ODBC wizard.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39947952
You do not set it in the ODBC Manager.  You set it in the SQL Configuration Manager as I showed in the image above.  On Windows XP it is also available thru the Administrative Tools.
0
 

Author Comment

by:RichardTatlow
ID: 39948471
I cannot do that because there is no SQL Configuration Manager installed with localDB, unlike my computers with SQL SERVER EXPRESS installed. The 'Program Files' folder contains a single 'Microsoft SQL Server' folder with a '110' subfolder containing nothing but the LocalDB files. There is no SQL SERVER program of any description in the Program Menu - the LocalDB server instance is run from a command prompt.

Also the named pipe is different each time the computer is started up and the LocalDB instance started.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39948815
Interesting, I thought you were talking about SQL Server / Express.  I found the info about LocalDB on this page: http://technet.microsoft.com/en-us/library/hh510202.aspx   The description of the connection requirements is somewhat confusing.  It looks like they are expecting you to connect using Visual Studio.

This page is about a 'command line management tool: SqlLocalDB.exe': http://msdn.microsoft.com/en-US/library/hh247716.aspx
0
 

Author Comment

by:RichardTatlow
ID: 39950066
Yes - I have read all of this and cannot find any relevant info - perhaps ODBC connections are not enabled. I shall put a question into MSDN and see what happens.
0
 

Accepted Solution

by:
RichardTatlow earned 0 total points
ID: 39966161
I have obtained the answer on MSDN. The server has to be named as '(localdb)\v11.0', access is Windows Integrated, database name =a2ztest2, attached file='C:\Temp\a2ztest2.mdf' and the test connection is successful and my application connects and runs OK. The named pipe appears to be accommodated automatically.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39967232
I'm glad you found it.  After I understood what you were looking for, it seemed like an odd thing.  Is it doing what you wanted now?
0
 

Author Comment

by:RichardTatlow
ID: 39967369
Yes - exactly as I would have hoped. I could not understand how the named pipe could be configured when it changes at each reboot and there is no reference to named pipes in the ODBC wizard. The key is the correct server name:  (localdb)\v11.0. Miss out the (localdb)\ bit and it does not work. The ODBC mechanism presumably must resolve the connection whatever the named pipe happens to be.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39967587
Interesting.  Thanks for posting the answer you found.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39968167
The key is the correct server name:  (localdb)\v11.0.
In case you are interested that is all covered in SQL Server connection strings

Just scroll down to the LocalDB section and click on the appropriate link.
0
 

Author Closing Comment

by:RichardTatlow
ID: 39988230
It works
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question