Wednesday, November 24, 2010

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

Scenario: You want to deploy a SQL Compact / .NET based application, using private deployment only. Your application depend on a certain .NET Framework version. The table below illustrates your options, as you can see there are special considerations, if your clients only have .NET 4.0 and not .NET 3.5 SP1 installed.

Installing the SQL Server Compact MSI also installs the required Visual C++ runtime files.

Also, notice that all files required for private deployment are included in the private folder in either Program Files or Program Files (x86) (C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private) for version 4.0.

For information on private deployment, see SQL Server Compact 3.5 SP2 Books Online and this blog post and this reply. Notice that 3.5 SP2 Books Online is only available “offline”.

UPDATE: Also see this blog post: http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html with a sample MSI project for a WPF app.

UPDATE 2: Also see this blog post for some advanced scenarios: SQL Server Compact Private Deployment tweaks

UPDATE 3: Private deployment of 3.5 SP2 article published

SQL Server Compact 3.5 SP2

SQL Server Compact 3.5 SP2 requires .NET 2.0 or later.

Application

Windows platform
SSCE 3.5 SP2
with .NET 2.0
SSCE 3.5 SP2
with .NET 3.5 SP1
SSCE 3.5 SP2
with .NET 4.0
XP (no .NET included)

NO

NO

NO

XP (.NET 2.0)

YES

NO

NO

XP (.NET 3.5 SP1)

YES

YES

NO

XP (.NET 4.0 only)

NO

NO

NO (1)

XP (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

Vista (.NET 2.0/3.0 included)

YES

NO

NO

Vista (.NET 3.5 SP1)

YES

YES

NO

Vista (.NET 3.0 and
.NET 4.0)

YES

NO

NO (1)

Vista (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

7 (.NET 3.5 SP1 included)

YES

YES

NO

7 (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

       

1: Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)  and/or Microsoft Visual C++ 2005 SP1 Redistributable Package (x64) must be installed.

SQL Server Compact 4.0 RTW

SQL Server Compact 4.0 RTW requires .NET 3.5 SP1 or .NET 4.0

Application

Windows platform
SSCE 4.0
with .NET 3.5 SP1
SSCE 4.0 RTW
with .NET 4.0
XP (no .NET included)

NO

NO

XP (.NET 3.5 SP1)

YES

NO

XP (.NET 4.0 only)

NO

YES

XP (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

Vista (.NET 2.0/3.0 included)

NO

NO

Vista (.NET 3.5 SP1)

YES

NO

Vista (.NET 3.0 and
.NET 4.0)

NO

YES

Vista (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

7 (.NET 3.5 SP1 included)

YES

NO

7 (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

     

22 comments:

Any Mouse said...

Is "Microsoft Visual C++ 2008 Service Pack 1 Redistributable Package" a STRICT requirement? Do you know if the Visual C++ 2010 Redistributable will work?

ErikEJ said...

Currently, that is a strict requirement, yes.

Unknown said...

I remember reading that SQLCE 4.0 requires .NET Framework 4.0 if we were to use ADO.NET and C# to interface with SQLCE. Is it true? Is it possible to use .NET 3.5 SP1 with SQLCE 4.0?

ErikEJ said...

As stated in the blog post, SQL CE 4.0 requires .NET 3.5 SP1 or later, so yes, it is very possible.

Any Mouse said...

I asked a while ago: "Is "Microsoft Visual C++ 2008 Service Pack 1 Redistributable Package" a STRICT requirement? Do you know if the Visual C++ 2010 Redistributable will work?"

I was wondering if this changed? I am using Install Shield Express and it does not have the option for Microsoft Visual C++ 2008 Service Pack 1 Redistributable Package BUT does have 2010. Any tips or advise?

ErikEJ said...

Yes, VS 208 SP1 C++ runtime is still required, but is included as Private file with 4.0 RTM - see http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html

Jason said...

We run an app in VS 2008 to insert records to SQL CE 3.5 database. The time was normal. However, when we run the same app in VS 2010 to add the records to the SQL CE 3.5, the duration increased to 30 times longer. Is this a compatibility issue between VS 2010 and SQL CE 3.5? Thanks.

ErikEJ said...

Jason: Could you share a repro? I have never come across this issue.

Hérold Jeanpois, Administrateur said...

Hi ErikEJ,

Instead of a comment, I have some questions. As a newbie, I'm running into a problem of not being able to access the database after privately deploying my webpage via my hosting. I had this type of error message:

Please correct the errors and try again.
The Provider encountered an unknown error.

All the necessary files were uploaded since I used WebMatrix for this purpose.

As well, when I tried to log in using the services (OAuth) openId, the page got frozen. Again the access to the database was denied.

What could that be? Do I need a App.config for wrie/read permission? If yes, how would it look like?

Thank you for your help.

Regards

ErikEJ said...

Have a look here, Herold:http://erikej.blogspot.com/2011/10/sql-server-compact-40-under-aspnet.html

Rama Charan said...

Need add in the table for one more item
Windows 7 (.NET 4.0)

ErikEJ said...

Rama: No, .NET 3.5 SP1 is always included on Windows 7

Unknown said...

Erik,

I'm trying to get this to work for an Outlook Addin application (64 bit machine, 64 bit Outlook). Basically I'm finding that if I include the 7 sqlce dlls (64 bit) in the root directory, my application will load fine. If I move those 7 sqlce dlls into the amd64 folder, my application doesn't seem to be able to find them and I get the following error:

Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8080. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

I've really tried everything I can think of in this post and also in some of your other related posts. Any ideas?

~Corey

ErikEJ said...

Corey: Due to a "flaw" in the way the ADO.NET provider locates the dll files, it will not work in an add-in DLL, as it looks in the folder where outlook.exe is installed. So yoru options are just include the x64 dlls with your dll or install the MSI (ie register in GAC)

Anonymous said...

Not work with Visual Studio 2008 + Pocket PC, error in System.Data.

I need use SQLCE 4.0 and not SQLCE 3.5.

ErikEJ said...

Unknown: SQL CE 4.0 is not supported on Pocket PC, as it is only available on x86 and amd64 desktop

mike said...

Erik, you helped me in the past getting a .Net 4.0 windows forms app to work with private deployment of SQL CE 3.5. Now I am trying to do the same thing but with a WPF app. I'm getting the "..unable to find the requested .net framework data provider." error. Is there a special consideration for WPF?

ErikEJ said...

Mike: no difference between winforms and wpf, what matters is if you use Linq to sql, Entity Framework or raw ado.net

mike said...

Erik, I have installed your SQL Server Compact Toolbox on Visual Studio 2010 sp1. I was able to convert my .sdf file to sql ce 4.0. But when I try to create an ADO.NET Entity Data Model, the only option is SQL CE 3.5. Should I be able to use a sql CE 4.0 db file to create an Entity Data Model?

ErikEJ said...

Mike: If you use the feature in the Toolbox to create the edmx file, it Works. If you use the official wizard, it only Work for v 4 with web projects

mike said...

Thanks Erik. It works. You are awesome!!!

ekareem said...

Thank you for the effort you are making to clarify issues around the deployment of this prodcut. It is not clear for me why Microsoft, in mnay cases, make the deployment such a difficult and unpleasant experience for the devloper. With .NET there was the impresssion that the DLL Hell is behind the MS developers, but I see that it will never go away. I suggest, for this product at least, that there will be a smart deployment wizard to be distributed with the DLLs for the main application. That smart wizard would then 'know all the complex rules' and take the correct actions for the application to work. It is very difficult to relase a software worlwide and not have it install perfectly. People loose their companies and thier jobs doing this. MS should look at things from the developer's side and recognize the pain involved.