SQL Server Compact 3.5 vs 4.0

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

SQL Server Compact 3.5 vs 4.0

Post by jeremyw » Wed 03 Dec 2014 00:28

Imagine someone has multiple installed versions (3.5 and 4.0) of SQL Server Compact. When using SDAC to create a new SQL CE database on this computer, which version will be used? Is this hard-coded, or is there a way to optionally select which version of SQL CE to use? Are there any functions we can call to determine which versions are installed (if any)?

We've been using SQL CE 3.5 (SP1 I think) up until now, but I'm wondering if we should make the switch to 4.0 (I also just downloaded 4.0 SP1). Have you experienced any significant differences (good or bad) with the newer versions?

Among other changes, I've read there are significant encryption changes in 4.0 (support for SHA2?). We use encrypted databases, so I wanted to make sure there is full support for the SQL CE 4.0 encryption.

azyk
Devart Team
Posts: 1072
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL Server Compact 3.5 vs 4.0

Post by azyk » Wed 03 Dec 2014 15:48

jeremyw wrote:Imagine someone has multiple installed versions (3.5 and 4.0) of SQL Server Compact. When using SDAC to create a new SQL CE database on this computer, which version will be used? Is this hard-coded, or is there a way to optionally select which version of SQL CE to use? Are there any functions we can call to determine which versions are installed (if any)?
When the TMSCompactConnection.Options.CompactVersion property is set to cvAuto, SDAC will detect the SQL Server Compact version, for which a database will be created in the following order: 4.0 -> 3.5 -> 3.0 . I.e., if the 3.5 and 4.0 versions are installed simultaneously on the PC, SDAC will create a database for the version 4.0.

In order for SDAC to use a definite SQL Server Compact version, set the TMSCompactConnection.Options.CompactVersion option to one of the following values: cv40, cv35 or cv30.

To learn how to detect the installed SQL Server Compact versions, read the following article at the Microsoft website: http://support.microsoft.com/kb/950550
jeremyw wrote:We've been using SQL CE 3.5 (SP1 I think) up until now, but I'm wondering if we should make the switch to 4.0 (I also just downloaded 4.0 SP1). Have you experienced any significant differences (good or bad) with the newer versions?
This question doesn't concern to SDAC functionality. Please forward this question with all your product requirements to SQL Server Compact developers - Microsoft.
jeremyw wrote:Among other changes, I've read there are significant encryption changes in 4.0 (support for SHA2?). We use encrypted databases, so I wanted to make sure there is full support for the SQL CE 4.0 encryption.
At present SDAC does not support this functionality. You can suggest this feature at our UserVoice ( http://devart.uservoice.com/forums/1046 ... components ). If the suggestion gets a lot of votes, we will consider the possibility to implement it.

jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

Re: SQL Server Compact 3.5 vs 4.0

Post by jeremyw » Wed 03 Dec 2014 19:50

Thank you for the quick and helpful response! I was digging through the SDAC documentation trying to find some of these answers, but I obviously didn't dig deep enough.

Incidentally, after seeing this, I looked back through the SDAC help. We're still using SDAC 6.11.23, but the help page for TMSCompactConnectionOptions.CompactVersion only references SQL CE 3.0 and 3.5. It's a minor issue, but it might be worth adding references to 4.0 now (especially since it takes priority over 3.5 by default).

azyk
Devart Team
Posts: 1072
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL Server Compact 3.5 vs 4.0

Post by azyk » Thu 04 Dec 2014 09:29

Thank you for your comment on our online documentation. We will correct this shortly.

Post Reply