8

I have created a LocalDB that I can work with in MS sqlserver management studio 2014, linqpad, and visual studio 2013.

Here is the part of the "connection property" dialog in SSMS which shows the server name: enter image description here

I would like to be able to connect to this database from within excel. The problem is that the "Data Connection Wizard" in Excel, fails to connect.

Here, I have typed in the same server name as was given from "connection properties" in SSMS....

enter image description here

And here is the error I get... enter image description here

Questions:

  • Can excel connect to a localDB at all? I know it CAN connect to sql-server databases. Is there some limitation about LocalDB that prevents this? I thought the whole point of LocalDB was to allow development without the effort of setting up a standalone database.

  • Is there an alternative way to connect? Or does my server name require some annoying modification?

Improve this question
2
  • 1
    Try .\mssqllocaldb, or localhost\mssqllocaldb, or <comptuersIPAddress>\mssqllocaldb. Which server name did you use for your (successful) VS and Linqpad connections?
    – Ƭᴇcʜιᴇ007
    Commented Mar 25, 2015 at 17:37
  • @Ƭᴇcʜιᴇ007, thanks, I copy/pasted exactly the server name found when I opened the properties dialog for the database in SSMS. No go on all three examples. The server name that works on linqpad and SSMS is exactly this... (localdb)\mssqllocaldb
    – Angelo
    Commented Mar 25, 2015 at 18:08

2 Answers 2

Reset to default
6

What a gratuitous P.I.T.A !

I was able to make it work by using the "Data Connection Wizard" following these steps....

  1. Select "Other/Advanced" from Data connection Wizard, then hit "next". I thought that localDB as created by sqlserver express would go as "SQL Server". Apparently not! though I can't fathom why.

enter image description here

  1. Select "SQL Server Native Client 11.0" as provider. OK, I had just NOT selected "SQL Server" in the previous tab. Also I happen to be running version 12 of sql server express and there is no "12" in the list-- perhaps it is strictly referring to client version, with the subtle implication that client 11 can connect to server 12? Yet another cognitive paper-cut.

enter image description here

  1. Enter the same server name that works in SSMS or linqpad. Select Windows Integrated security. Test Connection now works and it is possible to select the database, and dump a table into excel.

enter image description here

Its not difficult to do this, but there doesn't seem to be any logical flow to it you have to flounder around until something clicks.

Improve this answer
1
  • Dear, you are connecting to SQL Server 2014, which comes with SQLNCLI12, and in your screenshots you selected 'Native Client 11', which is SQLNCLI11, and for somereason, you don't have the 'Native Client 12' that excel looks for 2014.
    – tbc
    Commented Apr 16, 2015 at 23:55
1

This is the connection string (SQLServer 2012 / Excel 2013):
Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName={mdf file full path}

Oh on my PC, I had to use ;Trusted_Connection=Yes, it might not make any sense, since both my laptop and PC are running the EXACT same versions of SQLServer and Excel.

Edit

I can't reproduce the error (I even installed SQL Server 2014 on a VM and it still works). The only option you could add and might help you is Provider=SQLNCLI11; OR Provider=SQLNCLI12; you can find out what version is installed by running sqllocaldb v in command line.
That would produce something like: Microsoft SQL Server 2014 (12.0.2000.8)

Improve this answer
2
  • thanks, but it still gives an error. I had to remove your "server=" part to avoid getting "parseConnectParams()" in the error dialog. When I did that, it gave the same error as I got before.
    – Angelo
    Commented Mar 26, 2015 at 13:36
  • @Angelo I updated my answer, please do till the result once you try.
    – tbc
    Commented Mar 26, 2015 at 21:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .