SQL Compact 4.0 can't open/read sdf file

23
votes

I've installed SQL Compact 4.0 CTP 2 via WPI and used NUPack to add SQLCE.EntityFramework in my project. It compiles and run perfectly but I can't open the SDF file in Visual Studio 2010's Server Explorer.

When I try I always receive the following message:

Microsoft Visual Studio

This is not a valid SQL Server Compact Database file or this file version is not supported by current SQL Server Compact Engine.

OK

I've already tried to uninstall and reinstall everything.

It cannot be opened in SQL Server 2008 R2 Management Studio also.

Please help!!

3
visual-studio-2010sql-server-ce
There are currently no built-in tools in Visual Studio for working with SQL Server Compact 4.0 database files. (they will come with Vs2010 SP1). You can use my Visual Studio add-in sqlcetoolbox.codeplex.com or one of the other tools listed here: erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html – ErikEJ
The official Microsoft tools can be downloaded here. I found the link in this blog post. – David Silva Smith
I believe you can resolve this using this tutorial: msdn.microsoft.com/en-us/library/bb896150.aspx – cpoDesign

3 Answers

12
votes

Installing this (Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0) fixed this exact problem for me perfectly: http://go.microsoft.com/fwlink/?LinkId=212219

Props to ErikEJ from this thread: http://forums.asp.net/t/1811963.aspx/1

2
votes

I met the same problem while trying to follow up the tutorials of MVC3 (MvcMovie) provided by asp.net when I was told to double click the "Movies.sdf" file to check out the tables or other infos.

Acturally the solution is just below the request:

Note If you get an error when you double-click Movies.sdf, make sure you've installed SQL Server Compact 4.0 (runtime + tools support). (For links to the software, see the list of prerequisites in part 1 of this tutorial series.) If you install the release now, you'll have to close and re-open Visual Web Developer.

After Sql Server Compact 4.0(runtime+tools support) installed, the problem is solved.

0
votes

This question is a bit old. But I think the answer is that VS2010 SP1 has SQLCE 3.5 and SQLCE 3.5 x64 only, and not SQL Compact 4.0. The SQL products come together with Studio. There is still no SQLCE 4.0 today in 2012 included in distribution of Studio 2010 (I use Express edition).

This version packaging is not direct root cause, but just a clue, that different version will need extra effort.

The fix should involve registration of "Data Provider" in .NET Framework used by Visual Studio. Look at "DbProviderFactories" inside c:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config. This section describes what data providers can be used by .NET and Studio.

If there is unexpected entries there, try to repair framework or register providers manually. To do so, some low level registration tools will be required. Say for asp.net providers there is aspnet_regsql.exe etc. But for just SQLCE itself there must be something similar.

<system.data>
    <DbProviderFactories>
       <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>

The difference between broken Studio and working App is that both use the same Framework but have different providers in config. (Or app uses other technique to pick a DLL and open SDF)

Answer: Since SP1 it is officially possible to use SQLCE4.0 in Studio 2010 SP1 (Thanks to Nathan Fisher for link http://support.microsoft.com/kb/983509 )