Sunday, February 13, 2011 5:57 PM
I had Visual Studio 2010 Ultimate installed and working with SSCE 3.5 DBs. I then installed the following to use SSCE 4.0:
-VS2010 SP1 Beta
-SSCE CE 4.0
-SSCE Visual Studio Tools
-VS2010 SP1 Tools for SQL Server Compact 4.0
-SQL Express W/ Tools R2 (for Sql Management Studio 2008 R2)
The problem is that everything installed correctly but I really not able to use Compact 4.0 DBs. When I create them in VS as follows:
-New Project -> Console Application
-Add new item to project -> SQL Server Compact 4.0 Local Database
-Name it and click Add button
I get the following error:
"The version of the Microsoft SQL Server Compact database that you are adding to the project is not supported by the project type. The database will be added to the project, but data tools such as the Dataset Designer will not work with this database."
So it does add the DB to the project but without any framework around it, but I'm able to mess with the DB in the Server Explorer. I'm not able to use the DB with LINQ ADO.NET Entities.
Did I forget to set something up or install something important? This is driving me crazy as I have spent so much time trying to get this working. I've been on Eriks Blog (http://erikej.blogspot.com/) as well as Scott's Blog (http://weblogs.asp.net/scottgu/default.aspx) and have searched and search for an answer.
Anyone get this working? Or can anyone suggest anything?
Sunday, February 13, 2011 8:13 PMI have the exact same problem. I cannot get the entity framework to connect to the database either. The provider is not on the list, yet I can connect via server explorer. Hope someone can shed some light.
Sunday, February 13, 2011 8:42 PM
It is good to hear I'm not some fringe case.
I just went through the whole process again on my laptop and came to the same result.
Tuesday, February 15, 2011 3:21 PMModerator
The SQL Compact designers for 4.0 are only enabled for web projects, as desribed in this blog post: http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx
You can use workarounds, for example like I describe here:
Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
Tuesday, February 15, 2011 6:16 PM
Thanks Erik for helping to make things clearer. Although I read a lot on this some how I missed the "enable for web projects only" part.
I have started to try your workaround and I have gotten to the point of POCO Generation. I am wondering if this is needed as it wasn't referenced in Sriram's Blog (which is more Web related)? So I try to get down to just what is needed to get this working in my simple console app.
Wednesday, February 16, 2011 9:16 PM
OK I was able to get the console application solution working with a SSCE 4.0 database. After reading through Erik's and Sriram's blogs I got an idea to try and with a lot more finagling was able to work out errors that arose. So here is my Workaround for SSCE 4.0 with EDM in the non-supported project templates of Console Application:
First I used all default names, adjust appropriately if you don't. Unless stated all is done in Visual Studio 2010! Also you need to see my initial post and make sure you have everything installed and updated.
1. Without a solution open in Server Explorer click "Connect to Database" button and create a SSCE 3.5 DB (database1.sdf) in a separate directory (we used c:\dbs) and create the schema that you want to use.
2. Again in Server Explorer create a SSCE 4.0 DB (database1-4.sdf) in same directory duplicating schema (and data if you entered any) of SSCE 3.5 DB created in step 1
3. Delete the new connections in Server Explorer
4. Create a new Console Application (ConsoleApplication1)
5. Close Program.cs
6. In Solution Explorer right click on the project (ConsoleApplication1) select "Add" and then "Existing item"
7. Navigate to director where you created SSCE 3.5 DB (c:\dbs\database1.sdf) in step 1 and add it (if you don't see any files make sure you are looking at "All (*)" or "Data Files..." filter)
8. Select EDM in DataSource Wizard (if you don't see this you might need to remove some or all connections from Server Explorer and start this add step over)
9. Select "Generate from database" in EDM Wizard (Data Connection data should be filled in) then click "Next" button
10. Now check the "Tables" checkbox and click "Finish" button
11. Close the edmx file
12. In Solution Explorer open App.Config and edit:
provider=System.Data.SqlServerCe.3.5 -- to -- provider=System.Data.SqlServerCe.4.0 <- (there is only one)
13. In Solution Explorer open Model1.edmx with the XML (Text) Editor (not in the GUI Designer) and edit :
Provider="System.Data.SqlServerCe.3.5" to Provider="System.Data.SqlServerCe.4.0" <- (there is only one)
-- and --
ProviderManifestToken="3.5" to ProviderManifestToken="4.0" <- (there is only one)
14. Save and Close both files
15. Close the solution (save anything if asked)
16. In Windows Explorer go into the project directory of the solution (ours was c:/ConsoleApplication1/ConsoleApplication1) and delete the SSCE 3.5 DB (database1.sdf)
17. In Windows Explorer copy the SSCE 4.0 DB (database1-4.sdf) created in step 2 into the project directory of the solution (again c:/ConsoleApplication1/ConsoleApplication1) and rename it to database1.sdf
18. Back in Visual Studio re-open the solution (ConsoleApplication1)
19. In Server Explorer, refresh the Database1.sdf connection, right click and properties, version should be 4.0.xxxx.x
19. In Solution Explorer, open Model1.edmx, right click in the window and select "Update model from database", check the "Tables" checkbox and click "Finish" button, should be normal EDM output, now save the Model1.edmx file. If you get errors you will have to fix them in the edmx file with the XML editor.
20. TEST-Open Program.cs and insert your LINQ code and test it against the SSCE 4.0 DB
Some problems that I ran into when (re)generating the edmx were related to the databases not being the same (my bad). In these cases I was able to fix things by manually editing the edmx file.
I have since put this into our real application and we are testing that but so far so good. I also tested the Identity DB fields and they worked.
I still look forward to Microsoft fixing this in Visual Studio and hopefully soon.
I also noted that SSCE 4.0 doesn't work in SQL Management Studio. I'd love to see Microsoft fix this as well.
- Marked As Answer by patrick nelson Wednesday, February 16, 2011 9:19 PM
Friday, April 29, 2011 9:19 AM
Followed your instructions...
At step 19, when refreshing the database connection in server explorer, I encounter the following error:
"This is not a valid SQL Server Compact Database file or this file version is not supported by current SQL Server Compact Engine."
The point here to be considered is that I was successful in updating my model from sqlce4 database file. So I guess it will work with my application but I want to use graphical interface of Server Explorer to manage my sqlce4 database.
Friday, April 29, 2011 9:29 AMModeratorYou must install VS 2010 SP1 and the SQL Server Compact Tools: http://erikej.blogspot.com/2010/12/visual-studio-tools-for-sql-server.html
Please mark as answer, if this was it. Visit my SQL Server Compact blog
Friday, April 29, 2011 10:21 AM
I already had VS 2010 SP1 and SQL Server Compact Tools.
I reinstalled the sqlce tools.
The problem to me seems to be that the old connection was using SQL Compact 3.5 provider to connect to the database. When I add the new connection with SQL Compact 4.0 provider, it works fine.
So the conclusion in my opinion is to add the new data connection with SQL Compact 4.0 provider instead of just refreshing the existing old 3.5 provider in step 19 of above instructions.
Nevertheless, I really appreciate the work of Mr. Patrick for detailed instructions. And thanks to Mr. Erik too. :)
Friday, April 29, 2011 4:44 PM
This is VS2010 which can be persnickety at times. Not sure why you would have gotten those errors if you had SP1 and SCT installed. Maybe your reinstall set things in the proper way. But I did try to follow your explanation of step 19 and after trying to do this in VS2010 I'm not sure I follow what you mean.
Friday, April 29, 2011 7:48 PM
I also don't know whether the reinstall made it works.
But I dropped the existing connection (based on 3.5 database) and then add new connection with SQL Server Compact .NET provider 4.0 and that make it works.
Whatever the reason is I'm able to run it smoothly thanks to your detailed instructions.
Just wanna let you know that I did all these steps on my existing application which had SQL server express database connection with EF4. I successfully ported it to SQLCE 4.0 with EF4. :)
Friday, April 27, 2012 6:31 PM
A year later and this is still broken? WTF?
Friday, April 27, 2012 9:17 PM
Yeah it's still is a mess and now with SS2012 there seems to be no continuity between db products and tools which totally amazes me.
Microsoft needs to hire someone like us to set some continuity and connectivity between their apps and tools.<o:p></o:p>
But I've been working with SSCE4 dbs for a while now! And I tried to work with the localdb stuff of 2012 but I still like SSCE4 dbs better.<o:p></o:p>