No SQL Server Synchronization Supported

The SQL Server Compact 4.0 release does not support syncing of data with SQL Server using technologies like Microsoft Sync Framework, or merge replication, or remote data access (RDA).

Duplicate constraint names issue

The generation of the Entity Data Model for SQL Server Compact will fail if there are duplicate constraint names in the SQL Server Compact schema.

In SQL Server Compact, the constraint names are unique within a table and this can allow duplicate constraint names in the database. The behavior is different from SQL Server, where the constraint names are unique across the database. If a SQL Server Compact schema has duplicate referential integrity (primary key – foreign key relationship) constraint names, the generation of the Entity Data Model using the ADO.NET Entity Framework’s Entity Data Model Wizard will fail. The workaround is to change the name of the duplicate constraint name to be unique across the database, like by adding the name of the table to the constraint name.

52 comments:

Erik, Is it possible to use SqlCE 4.0 with WinForms project? It would be really helpful with a tutorial for any workarounds necessary to do that. And with discussion of any limitations. Thanks a lot for great blogs!

What is the best way to use SQL CE4 with a Silverlight OOB running with elevated trust? And how would you deploy to the client? It would also be nice to use SQL CE4 with Blend for "Design Time Data" that is more realistic. (these questions were also asked at scottgu blog)

James: Silverlight and SQL CE is really not supported, but I have a blog post, that shows what can be done.The SQLCE MSI must be installed on the client machine for this to work. http://erikej.blogspot.com/2010/02/access-local-sql-compact-database-from.html. Re Blend: Suggest this to the Blend team on Connect

Thanks for posting the direct URL for the download, as WebMatrix' install hung on me for a long time.

What I can't find is the BoL for CE 4. I need the docs to see whether I have to adjust my SQL Server SQL generator in my ORM to meet the new features in CE4. I have code paths for CE 3.5 but if CE4 is compatible with (mostly) normal SQL Server syntaxis, I'd like to add that too so more features are supported (like DELETE FROM FROM, dunno if that's supported in CE4, so I'd like to see docs ;))

Frans: SQLCE 4 BOL are not available yet, I will update this blog post when they arrive, For SQLCE 4 T-SQL syntax docs, use SQL Server 3.5 SP2 BOL: http://bit.ly/ekRnJp and this blog post http://bit.ly/es3VYd

If you are deploying a 32 bit only application that depends on SQL Server Compact Edition 4.0, it looks like you have to switch at install time to run the appropriate installer, either: SSCERuntime_x86-ENU or SSCERuntime_x64-ENU. If you are privately deploying, do you have to also switch which private bits are installed?

Fred: Have you seen the installation advice here: http://blogs.msdn.com/b/sqlservercompact/archive/2011/03/15/sql-server-compact-4-0-tooling-support-in-visual-studio-2010-sp1-and-visual-web-developer-express-2010-sp1.aspx

Erik thanks for response. There are a few options thy would work Bungie looks of things. However, the webserver OS is Linux (which I don't believe SqlCE is compatible with?) so looks like will have to be another DB incarnation that we go for.

Erik, I have a project that is using SQL Compact 4.0 as the database. I am using installshield express as my setup project. Currently installshield does not show SQL Compact 4.0 as an option to install with my application. I spoke with a support person of installshield and she said to use a merge module. Does one exist for SQL Compact 4.0? If not which .dll's should I install?

Heloo Sir We are Using SQL Server Compact 3.5 as db while we are compiling project in Visual Studio it will shows an error like path not found.. we give full path in Data Source section in connection String... hope that you can help me..

A funny thing is that when I copy the exe to the directory with SQL 4.0 CE dlls and start it from there - everything works. But if I change dir to any other folder so that current path does not contain SQL dlls - it shows an error.

I have a multi project solution with a Winforms Application and Class Library Project with all the data components in it. I am trying to deploy the project using ClickOnce, but when I do, I do not see the .sdf file(Sql CE 4.0) anywhere. The program works fine though. I assume it gets embedded into the dll's that are created for the Class Library.

If this is the case, is there any way to make it a separate file? I would like to be able to make changes manually if needed.

I just realized ClickOnce was putting it in some weird folder. However, now I messed up and deleted that copy of the Solution now I am recreate what I was doing.

Now when I try to publish with ClickOnce, I am getting errors because the project can not find the database. I go to the folder that the database was in and it is blank. Do you know how I can tell ClickOnce to add the .sdf file in the installation file?

When I go to Application Files under the publish tab, I do not see the Sql CE database because it is in a different project.