SQL Server CE FAQ

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

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

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.

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!

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

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

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.

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)

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.

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.

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 ]”

Leave a Reply

Your email address will not be published. Required fields are marked *