Everything SQL Server Compact: SQL Server Compact 4.0 released!

Thursday, January 13, 2011

SQL Server Compact 4.0 released!

WebMatrix has been released and will be launched at CodeMash on Thursday 13/1. Sign up for the live streaming event here.

Download locations

At the same time, SQL Server Compact 4.0 (build 4.0.8482.1) has been released to web, and is available via Web Platform Installer 3.0 and also available for download here:

Microsoft SQL Server Compact 4.0 (x86 and x64)

SQL Server Compact 4.0 Books Online

Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0

(Team blog post about the VS 2010 SP1 tooling support)

Deployment improvements

The “Private” folder contains both the x64 and x86 related DLL files, and also contains the required C++ runtime DLL (new in RTM):

I will update my “Private Deployment” blog post with the new information.

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.

I have worked around this limitation in my SQL Server Compact tools, SQL Server Compact Toolbox and SQL Server Compact Scripting Tools

52 comments:

Naaerdetnok said...: 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!; January 13, 2011 at 12:43 PM
ErikEJ said...: Look at my EF walkthroughs, the DAL in there could also be used for WinForms wothout any issues. Or you can use plain Entity Framework datasources directly; January 13, 2011 at 1:07 PM
Anonymous said...: 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); January 13, 2011 at 4:58 PM
ErikEJ said...: 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; January 13, 2011 at 5:48 PM
Unknown said...: Erik,
what about synchronization features in SSCE 4? Shell we forget about merge, sync and RDA in future? incredible!; January 14, 2011 at 9:58 AM
ErikEJ said...: Baloo: No, I would imagine that the server tools are just not ready yet, I expect them to appear in the future (SPx for example); January 14, 2011 at 10:19 AM
Frans Bouma said...: 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 ;))

Perhaps they're not uploaded yet?; January 14, 2011 at 11:05 AM
ErikEJ said...: 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; January 14, 2011 at 11:23 AM
Owner said...: In previous version of SQL CE, I use SQL Server Management Studio Express as primary tool to do SQL CE development. Does SQL CE 4.0 compatible with SSMSE?; January 14, 2011 at 4:33 PM
Amastaneh said...: Erik, would you please compare between SQL Server 2008 R2 Express and SQL Server Compact 4.0?; January 17, 2011 at 2:33 AM
ErikEJ said...: Amir: Good idea, for now there is this (slightly old) doc: http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx; January 17, 2011 at 8:28 AM
ErikEJ said...: Owner: No, I do not expect SQL CE 4.0 to work with SSMS(E). You can use Visual Studio or one of the other tools listed on my 3rd party tools page.; January 17, 2011 at 8:36 AM
Mike said...: Would you mind describing what needs to be done in a Visual Studio setup project to include the CE 4.0 binaries with a WPF app, please? Thanks!; January 19, 2011 at 3:58 PM
ErikEJ said...: Mike: Good idea, I will put that on my list.; January 20, 2011 at 11:58 AM
FAQQ said...: Still facing this issue: http://social.msdn.microsoft.com/Forums/en/sqlce/thread/d4d38d52-3f3c-4d73-a12c-9b7b28cabc0c

Still required to install the runtime at the server? Trying with the hosting company as we speak, but a bummer nonetheless.; February 1, 2011 at 1:35 PM
ErikEJ said...: FAQQ: I will investigate!; February 1, 2011 at 5:38 PM
Libbi said...: Do you know if the new jdbc driver 4.0 will work with the sql server compact 4.0?; February 21, 2011 at 7:19 PM
ErikEJ said...: Libbi: I am sure it will not.; February 21, 2011 at 8:55 PM
Stefan said...: I am trying to delete several record with EF4, the TransactionScope seams not to work... there is any workaround for this? or CE 4 does not support transactions from EF ?

Thanks; February 28, 2011 at 3:06 PM
ErikEJ said...: Stefan: Suggest you ask in the MSDN forum, and possibly share some code there.; February 28, 2011 at 3:15 PM
Unknown said...: Hi .. I would like to know if SQL Compact Edition 4 is compatible with Windows 7 starter edition? thanks; March 23, 2011 at 3:06 AM
ErikEJ said...: Kitty: Yes, all Windows 7 editions are supported; March 23, 2011 at 7:19 AM
Daniel McGloin said...: 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?; April 7, 2011 at 9:49 PM
ErikEJ said...: Daniel: I think you can just run: SSCERuntime-ENU.exe /Q. For private deployment, just include everything, the additional files will just be ignored.; April 8, 2011 at 8:07 AM
Unknown said...: Can I use SQL Server Compact 4.0 on Microsoft-IIS/6.0 on Windows 5.2.3790.131072 (Win32NT) with DotNet 4 installed?; May 3, 2011 at 7:22 AM
ErikEJ said...: Moiz: Yes, you can.; May 3, 2011 at 8:00 AM
Saracen9 said...: Hi Eric, is SqlCE compatible with .php? Need to develop a small, lightweight back-end for a website (written in php - can't change) and was considering SqlCE; May 4, 2011 at 9:06 PM
ErikEJ said...: Currently, you can use OLEDB (ADO Classic) or ADO.NET to acess SQL Server Compact. Does any of these work with PHP, or do you require and ODBC provider?; May 4, 2011 at 10:16 PM
Unknown said...: I installed the VS 2010 SP1 Tools for SQL Server Compact 4.0 from both the file download as well as Web Platform Installer, and I STILL can't get it to show up in my Choose Data Source dialog in VS.; May 7, 2011 at 3:34 AM
ErikEJ said...: 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; May 7, 2011 at 8:55 AM
Saracen9 said...: 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.; May 10, 2011 at 9:02 AM
Anonymous said...: 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?; June 21, 2011 at 9:47 PM
ErikEJ said...: Andrew: Suggest you just include the DLL files, as I describe here: http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html; June 25, 2011 at 12:47 PM
Unknown said...: how can i use BULK INSERT in webmatrix? thank you..; July 1, 2011 at 7:52 AM
ErikEJ said...: Nic: BULK INSERT does not exist in SQL Server Compact, but you can use my SqlCeBulkCopy library: http://sqlcebulkcopy.codeplex.com; July 1, 2011 at 2:06 PM
Tony Tom.K said...: 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..; July 7, 2011 at 9:39 AM
ErikEJ said...: Hi Tony, suggest you as in the MSDN forumn, and provide some sample code.; July 7, 2011 at 4:09 PM
Anonymous said...: Hi, Eric,

I have a little problem with SqlCmd 1.2.0.6. It worked just fine until yesterday, when I installed MS Visual Studio 2010. From now on the utility does not start saying:

HRESULT 0x2 level 16 state 1
Named pipes - cannot open connection to SQL server [2].

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'm ready to collect dumps if you want to fix the issue.

Thanks anyway.; August 10, 2011 at 9:49 AM
ErikEJ said...: Please post as much info as possible to the Issue list on htt://sqlcetoolbox.codeplex.com, including the following file: C:\Users\\AppData\Local\SqlCe40ToolboxExe.sdf; August 10, 2011 at 9:55 AM
Anonymous said...: Eric,

Don't worry, I found the reason. There is another sqlcmd - the one from Microsoft. It was installed along with VS 2010:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE

And, of course, Microsoft has put the directory into the PATH environment variable.

Perharps this was my fault - I renamed your magic utility from SqlCmd40 to SqlCmd and Microsoft did not like it.

Anyway, thanks for your soft.

With Best Regards,
Sergey Kochkarev; August 10, 2011 at 10:11 AM
Anonymous said...: Erik,

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.

Thanks,
Andrew Petersen; September 16, 2011 at 4:36 PM
ErikEJ said...: Andrew: The SQL Compact database is always a separate file.; September 16, 2011 at 9:13 PM
Anonymous said...: 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.

This is a multi-project solution with the data in a class library.

Thanks,
Andrew; September 16, 2011 at 10:43 PM
ErikEJ said...: Andrew: This sounds like a Click-Once related question, I cannot assist on that. One opetion to consider is to create the database programatically instead.; September 18, 2011 at 1:10 PM
prasadkulkarni said...: Hi,
I want to know whether there is any mechanism similar to triggers, which can help me tracking inserts/updates/deletes on tables in SQL server compact 4.0 database?; August 22, 2012 at 12:19 PM
ErikEJ said...: Prasad: No, there are no triggers, but you can trak in your data access layer; August 22, 2012 at 12:50 PM
J Vitriolo said...: We encountered a problem on SQL CE 4.0 when we deployed it on XP SP3 it seems that our application cannot open/access the .sdf file, but there are cases that it works properly fine on other xp sp3.; November 19, 2012 at 4:30 AM
ErikEJ said...: Vitriolo: Suggest you ask in the MSDN forum, and provide more detail; November 19, 2012 at 2:52 PM
dyaz said...: Erik
I need to know, how to configure my model of my program of visual studio 2012, that work with SDF 4.0, why have this error:
SQL Server Compact/SQLite Toolbox

Error Code: 80004005
Message : El archivo de base de datos es mayor que el tamaño máximo de base de datos configurado. Este valor es efectivo solo en la primera conexión simultánea de la base de datos. [ Required Max Database Size (in MB; 0 if unknown) = 0 ]
Minor Err.: 25104
Source : SQL Server Compact ADO.NET Data Provider

Help Please; November 25, 2014 at 5:30 PM
ErikEJ said...: Dyaz: Add Max Database Size=4091 to your connection string; November 25, 2014 at 6:47 PM
Unknown said...: Hello Erik can I use for mvc 3 music store template i mean to say i need to put sample data.; December 16, 2016 at 8:21 AM
ErikEJ said...: Farhad: I do not understand your question, suggest you ask on the Github issue tracker, so we can have a conversation...; December 16, 2016 at 8:58 AM