Cannot be opened because it is version 852. this server supports version 782 and earlier

Question

I am using Visual Studio 2017 and SQL Server 2014. While attaching a database file to Visual Studio, I get this error:

""

After upgrading the file I used this connection string

<connectionStrings>
    <add name="con" 
         connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\binaaelmamorra.mdf;Integrated Security=True;Connect Timeout=30"/>
</connectionStrings>

It's working fine on my machine, but on the client machine, an error pops up saying

Cannot be opened because it is version 852. This server supports version 782 and earlier

Though I installed SQL Server 2016 local DB on the client side, I still can't get rid of it.

My second question is: what is the current instance of SQL Server to which the database file is not compatible?

Percentile

v11.0 was an example provided by @marc_s, In your case the command would have been sqllocaldb i ProjectsV13 – Shrug

Answer 1

This worked for me :

1. open command prompt and type :

   cd "C:\Program Files\Microsoft SQL Server\130\LocalDB\Binn"
   

2. Run this command in cmd to delete the local db :

   SqlLocalDB.exe delete "MSSQLLocalDB"
   

   (or SqlLocalDB.exe stop "MSSQLLocalDB", if you get an error of intance still running)

3. Then type in this command to create a new updated instance of local db :

   SqlLocalDB.exe create "MSSQLLocalDB"
   

Reference : https://www.youtube.com/watch?v=bCjc9Mmx-bM

Kneepad

Answer 2

It seems you have multiple instances of various versions of SQL Server on your machine.

For the "full-blown" versions (Express and up), you can check what version they are by looking at the SQL Server Configuration Manager:

For the "LocalDB" versions of SQL Server (developer-oriented versions), you can use the sqllocaldb command line tool.

Get an overview of what you have on your machine using this command:

C:\> sqllocaldb i

MSSQLLocalDB
v11.0

Then, you can check the individual instances using the details command:

C:\> sqllocaldb i mssqllocaldb

Name:               mssqllocaldb
Version:            13.1.4206.0
Shared name:
Owner:              MSE-PC-02\Marc
Auto-create:        Yes
State:              Stopped
Last start time:    27.08.2017 12:24:45
Instance pipe name:


C:\> sqllocaldb i v11.0
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              MSE-PC-02\Marc
Auto-create:        Yes
State:              Stopped
Last start time:    26.08.2017 15:41:18
Instance pipe name:

So this tells you that there are two instances of SQL Server LocalDB available - mssqllocaldb is version 13.1 (SQL Server 2016), while the v11.0 instance is version 11.0 (SQL Server 2012).

So if you want to attach a .mdf file to any one of those two instances, you just need to pick the right one in order to get it working with the version you want. And once you've "upgraded" a database file to a newer version of SQL Server (like 2016 - internal DB version 852), you can NEVER go back - there's no way to detach/attach, backup/restore or other ways to get those binary files to an older version of SQL Server. You would have to script out the structure (and possibly data) of your newer database file to .sql files and run those on the older version.

Elianore

How do you pick which one? My Web.config looks like this ' Data Source=(LocalDb)\v11.0 ' but for some reason, it seems to want to use the v13.1 MSSQLLocalDB. – Leffen

@PKirby: that's defined by the connection string - if you have Data Source=(LocalDb)\v11.0 - it will use the v11.0 instance; if you have Data Source=(LocalDb)\MSSQLLocalDb, it will use that other instance – Elianore

Answer 3

Version 852 is SQL Server 2016, and version 782 is SQL Server 2014. A good reference for the versions can be found at this page.

Based on your explanations, this should be a version 782 DB. Since it is version 852 DB, this means that it was upgraded using SQL Server 2016, not SQL Server 2014. Accordingly, you should check for all versions of SQL Server running on your local / dev machine, to confirm that only SQL Server 2014 is in use, and not (potentially) SQL Server 2016 installed by VS 2017.

Additionally, it sounds like the version on your end user is 2014, not 2016. This is potentially an issue if the current DB is a SQL Server 2014 mode DB. Try explicitly connecting to TempDB before connecting the DB. (Note: This is something I had to do when encountering a 2014/2016 incompatibility, though I can't recall what the actual issue was.)

Illuminate

I agree to your statements, but what can I do on client side, the client has both SQL server 2014 and SQL server 2016 installed, – Percentile

Answer 4

If you want that your project generates a SQL Server 2014 Database which you can attach on the client machine,F you have to create an instance of the server on your machine. To do this, open the command line in administrator mode and enter the command "sqllocaldb create v12.0 12.0". This creates a server instance called "v12.0" in version 12.0, SQL Server 2014.

If the server has not yet been started, the server must be started with the command "sqllocaldb start v12.0".

Now you have to change your conectionString like this:

<connectionStrings>
    <add name="con" 
         connectionString="Data Source=(LocalDB)\v12.0;AttachDbFilename=|DataDirectory|\binaaelmamorra.mdf;Integrated Security=True;Connect Timeout=30"/>
</connectionStrings>

Maybe you have put some parameters to your app.config

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v12.0" />
      </parameters>
</defaultConnectionFactory>

Brandt

I have started the sqllocaldb v12.0 but when I attach the mdf file it forces me to upgrade the mdf file to the higher version v13. – Percentile

Answer 5

In my case I had once updated the database with entity framework code first technique using the latest version of Visual Studio then I tried to run the project with an older version and it showed this exception. I fixed it by just removing the database and re-running the command Update-Database -verbose -verbose flag is so you can see what commands were executed.

Suet