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

     

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

mmgade 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, Administrator 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

Corey Leichty 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)