Thursday, January 13, 2011

SQL Server Compact 4.0 released!

image_thumb[1]

image_thumb[5]

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)

Related blog posts

ScottGu has just issued a long blog post about using SQL Server Compact 4.0 with WebForms + Entity Framework Database First and MVC + Entity Framework Code First.

The SQL Compact team has a concise feature overview. And release announcement, which also includes information about scenarios not enabled with SQL Server Compact 4.0.

I have blogged about SQL Server Compact 4 at several occasions:

Getting started with SQL Server Compact 4.0 and ASP.NET 4.0 (no WebMatrix)

SQL Server Compact 4.0 news roundup

Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

SQL Server Compact version detector

SQL Server Compact 4.0 ASP.NET Membership provider

SQL Compact 4.0 now available as a .zip file

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Visual Studio Tools for SQL Server Compact 4 now available

SQL Server Compact “Private Deployment” on desktop–an overview

Comparison of SQL Server Compact 4 and SQL Server Express 2008 R2

Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

Using SQL Server Compact 4.0 with Desktop Private Deployment and a Setup project (MSI) (part 2)

Access SQL Server Compact 4 with ASP Classic and VbScript

Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

Visual Studio 2010 Service Pack 1 with support for SQL Server Compact 4.0 released

Snapshot Synchronization with SQL Server Compact 4.0

Useful new topics in SQL Server Compact 4.0 Books Online

Saving images to SQL Server Compact with Entity Framework 4.1 Code First

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):

image

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!

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

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)

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

Unknown said...

Erik,
what about synchronization features in SSCE 4? Shell we forget about merge, sync and RDA in future? incredible!

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)

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?

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

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?

Amastaneh said...

Erik, would you please compare between SQL Server 2008 R2 Express and SQL Server Compact 4.0?

ErikEJ said...

Amir: Good idea, for now there is this (slightly old) doc: http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx

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.

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!

ErikEJ said...

Mike: Good idea, I will put that on my list.

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.

ErikEJ said...

FAQQ: I will investigate!

Libbi said...

Do you know if the new jdbc driver 4.0 will work with the sql server compact 4.0?

ErikEJ said...

Libbi: I am sure it will not.

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

ErikEJ said...

Stefan: Suggest you ask in the MSDN forum, and possibly share some code there.

Unknown said...

Hi .. I would like to know if SQL Compact Edition 4 is compatible with Windows 7 starter edition? thanks

ErikEJ said...

Kitty: Yes, all Windows 7 editions are supported

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?

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.

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?

ErikEJ said...

Moiz: Yes, you can.

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

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?

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.

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

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.

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?

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

Unknown said...

how can i use BULK INSERT in webmatrix? thank you..

ErikEJ said...

Nic: BULK INSERT does not exist in SQL Server Compact, but you can use my SqlCeBulkCopy library: http://sqlcebulkcopy.codeplex.com

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..

ErikEJ said...

Hi Tony, suggest you as in the MSDN forumn, and provide some sample code.

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.

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

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

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

ErikEJ said...

Andrew: The SQL Compact database is always a separate file.

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

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.

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?

ErikEJ said...

Prasad: No, there are no triggers, but you can trak in your data access layer

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.

ErikEJ said...

Vitriolo: Suggest you ask in the MSDN forum, and provide more detail

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

ErikEJ said...

Dyaz: Add Max Database Size=4091 to your connection string

Unknown said...

Hello Erik can I use for mvc 3 music store template i mean to say i need to put sample data.

ErikEJ said...

Farhad: I do not understand your question, suggest you ask on the Github issue tracker, so we can have a conversation...