SQL Server CE FAQ

SQL Server CE v3.1 Downloads and Information

Administrator — Fri, 07 Sep 2007 08:11:00 GMT

Q: Is there a good whitepaper/documentation that describes the SSCE components?

A: Yes there is a knowledge base article, http://support.microsoft.com/?kbid=920700, lists the major changes (since v3.0), all relevant downloads, performance gains in the new runtime and a list of known issues with v3.1 (mostly related to working under Vista).

Database Encryption

Administrator — Fri, 07 Sep 2007 07:56:00 GMT

Q: When I go to upgrade my database there are a number of Encryption options. What do they all mean?

A: To support older operating systems, with older encryption algorithms, and newer, more secure encryption, SQL Server Compact 3.5 supports backward-compatible encryption modes. The encryption modes available in the Upgrade to SQL Server Compact 3.5 Database dialog box are as described in the following list.

Platform Default: When you create or upgrade SQL Server Compact databases, Encryption mode will select the default encryption for the operating system. By creating a database on one of the newer platforms shown here, older platforms (such as Windows CE 4.2 or Windows Mobile 2003) will be unable to open encrypted SQL Server Compact files.
Engine Default: By default, the encryption mode for the SQL Server Compact 3.5 database file will not work on Windows CE 4.2 or Windows Mobile 2003 Software for Pocket PC. This encryption mode will work on all newer SQL Server Compact 3.5 supported platforms.
PPC2003 Compatibility: While less secure, this option can be used to support older and newer operating systems

[Sources from Ambrish's post on the SQL Server CE 3.5 forum]

SSCE 3.5 Upgrade Process

Administrator — Fri, 07 Sep 2007 07:31:00 GMT

Q: I've attempted to open a v3.1 SSCE database file using the SSCE 3.5 runtime (still in beta) and it is failing?

A: Despite using the same extension (ie .sdf) not only are SSCE 3.5 db file not backward compatible with older runtimes, SSCE 3.1 and earlier db files are not compatible with the new runtime. If you are building an application using the new SSCE 3.5 runtime you need to make sure you call the upgrade api on the SqlCeEngine class. Alternatively you can use Visual Studio 2008 to connect to an old database file, which will prompt you to upgrade.

Lock Manager has run out of space

Administrator — Thu, 06 Sep 2007 06:08:00 GMT

Q: What does the following error message mean?

The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space

A: As the error message implies it is caused by large transations but the question is how large? According to the product team the limit on the number of lock references in v3.0/v3.1 was 2^18-1 which equates to 1Gb of pages. In v3.5 this has been increased to 2^32-1 or 17.6Tb of pages!

Merge Replication Compression Level

Administrator — Tue, 05 Jun 2007 08:12:00 GMT

Q: How do enable compression when using SQL Server Merge Replication?

A: There is a property on the SQLCeReplication object appropriately named CompressionLevel that can be used to determine the level of compression that is used. Valid values are 0 (no compression), 1 (default) through to 6 (maximum compression). More information is available via MSDN

Bug Resolution

Administrator — Mon, 19 Mar 2007 22:37:00 GMT

Q: A Select statement involving an inner join between two tables, one with an index on the join column and the other has no index, is returning incorrect results?

A: There is a known issue which has been acknowledged in this knowledge base article

Getting started with Merge Replication

Administrator — Sat, 10 Mar 2007 21:45:00 GMT

Q: I want to use Merge Replication to synchronise data to my SQL Server CE database. Are there any how-to whitepapers available?

A: In addition to the SQL Server 2005 Compact Edition Books Online there is also an article on GotDotNet that walks through synchronising against a SQL Server publication using Merge Replication.

Relationships

Administrator — Sat, 10 Mar 2007 01:43:00 GMT

Q: How do I create relationships between tables in SQL Server CE?

A: Unfortunately there is currently no designer support (unlike for SQL Server 2005) for building relationships between tables in SQL Server CE. To build relationships you need to use SQL commands such as:

ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)

Security Problems

Administrator — Tue, 06 Mar 2007 22:22:00 GMT

Q: I'm having difficulty understanding the security model between SQL Server CE, IIS and SQL Server. Are there any good reference papers on this?

A: Yes, Microsoft are continually updating Documents Online but there are a couple of additional whitepapers that maybe of interest:

Renaming Tables

Administrator — Thu, 01 Mar 2007 16:06:00 GMT

Q: How do I rename an existing table?

A: Although SQL Server Compact Edition does currently support this through the SQL language it is exposed at the lower level as Joao explains in this article across at the Pocket PC Developer Network.

IsNull v's Coalesce

Administrator — Sat, 17 Feb 2007 07:26:00 GMT

Q: I've been using the IsNull function but it seems to be ignoring the second parameter (just returning True or False instead). Is this function supported and/or is there an alternative?

A: Officially SQL Server Compact Edition doesn't support IsNull, although the described behaviour is accurate despite not throwing a parse error or being documented. The supported function is Coalesce which behaves in a similar fashion, returning the first non-null argument.

SQL CE Missing in Object Explorer

Administrator — Sat, 17 Feb 2007 07:18:00 GMT

Q: I've installed all the components for SQL Server Compact Edition yet I'm still getting SQL Mobile appear in the Object Explorer within SQL Server Management Studio (SSMS)?

A: In order for the SQL Server Compact Edition elements to appear you need to install SP2 for SSMS. This is documented in the knowledge base on the features & issues of SQL Server Compact Edition v3.1 available here.

Using SQL Server Management Studio Express

Administrator — Sat, 17 Feb 2007 07:12:00 GMT

Q: Can I use SQL Server Management Studio Express to create and work with SQL Server Compact Edition databases?

A: Yes, but not with the original RTM version (available here). You need to install a version which includes SP2 (currently available as a CTP here)

Support for System.Transactions

Administrator — Sat, 17 Feb 2007 06:56:00 GMT

Q: Does SQL Server Compact Edition support System.Transactions.Transaction?

A: No (as supported by the product team in their comment here) it is currently not supported in v3.1. Indication is for support in the next version of Visual Studio which should correspond to v3.5.

Schemas

Administrator — Sat, 17 Feb 2007 06:48:00 GMT

Q: Does SQL Server Compact Edition support the concept of Schemas?

A: No, there is no support for schemas. In fact the query engine will complain if you try to include schema prefixes - for example Select * From dbo.Companies will throw an error similar to "The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = companies ]"