0

I'm confused about how to connect to LocalDb from SQL Server 2014 Management Studio. (I also have SQL Standard Edition installed on localhost.)

I have a connection in my ASP.NET application like this:

<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\BillTest.mdf;Initial Catalog=BillTest;Integrated Security=True" 
     providerName="System.Data.SqlClient" />

When I go into SSMS 2014 and connect to (LocalDb)\v11.0 I can connect and I see a database like aspnet-BillTest-2015mmddhhmmss but I can't open it. I get an error like "the database ... is not accessible."

When I connect to (LocalDb)\mssqllocaldb I can connect, and I see a DB like BillContext and there are my tables. I verified this is the one the app is connected to, by inserting new rows and seeing them show up in my app.

Can you explain the discrepancy in connect strings:

  • how is my ASP.NET application using (LocalDb)\mssqllocaldb when the connect string in Web.config is (LocalDb)\v11.0? What is actually on \v11.0?
  • And why does (LocalDb)\mssqllocaldb point to a SQL version (12.0.2000) that is neither 11.0 nor my SQL Server 2014 installation (localhost = 12.0.4100.1)?
  • why is the DB name "BillContext" instead of "BillTest"?
wrschneider
  • 17,913
  • 16
  • 96
  • 176

2 Answers2

1

It's because of the LocalDB Server Version and SSMS version. Till Visual Studio 2013 you were supposed to use version specific connection strings (e.g (LocalDb\v11.0) from Visual Studio 2015 onwards you are supposed to use version independent connection strings (LocalDB\MSSQLDB) in order to work properly. Check this link for reference.

Version independent local DB in Visual Studio 2015 - Bill Wagner

ruffin
  • 16,507
  • 9
  • 88
  • 138
0

  1. why does (LocalDb)\v11.0 work from my ASP.NET application but not within SSMS?

    It sounds like (LocalDb)\v11.0 is accessible from both from what you describe, it's just that the database itself isn't accessible. I'd check if the file still exists or if something else has locked the file.

  2. why does (LocalDb)\mssqllocaldb point to a version (12.0.2000) that is neither 11.0 nor my SQL Server 2014 installation (localhost = 12.0.4100.1)?

    Assembly version and file version are different. See this answer

  3. why is the DB name "BillContext" instead of "BillTest"?

    You're probably not providing a connection name to the EF context (e.g. new BillContext("DefaultConnection")) so it's falling back to the default, which is a LocalDb database named after your context.

Community
  • 1
  • 1
jjj
  • 4,822
  • 1
  • 16
  • 39
  • #3- thanks, that explains why there's a DB called BillContext on `LocalDb\mssqllocaldb`. and why it's called BillContext. I still don't understand the separate v11.0 connect string and what the aspnet-BillTest-timestamp DB is all about. – wrschneider Jul 25 '15 at 01:41
  • `aspnet-BillTest-2015mmddhhmmss` looks like the default database name used when you make a web project from a template. Does the corresponding `.mdf` file still exist or was it deleted? – jjj Jul 27 '15 at 17:08
  • Yes there is a file called BillContext.mdf in App_Data. I understand why I have a `BillContext` DB under `LocalDb\mssqllocaldb` but not totally sure what the aspnet-BillTest-.. one is for or why it's inaccessible. – wrschneider Jul 27 '15 at 17:19
  • @wrschneider: I was actually asking if the file for `aspnet-BillTest-2015mmddhhmmss` still exists, since it's possible for the file to be deleted but the database to still be listed in SSMS, and if the file's gone, there's not much you can do with the database outside of [detaching it](http://stackoverflow.com/questions/13411005/how-to-delete-localdb-database-if-the-file-is-gone). It sounds like a possibility is that the database was created when the project was first created, and the file was later removed. – jjj Jul 27 '15 at 17:51
  • Ah, that might be it. I was futzing with the connect string and it's possible that's what happened before I got it right. That might also explain the v11.0 / mssqllocal discrepancy. – wrschneider Jul 27 '15 at 18:23