SQL Server CE Portal

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.

SQL Server Compact Edition 3.5 Beta 2 Downloads Available

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

1. SQL Server Compact 3.5 for Windows Desktop Beta 2: Installs the files for SQL Server Compact 3.5 and Microsoft Synchronization Services for ADO.Net for Windows Desktop platform

2. SQL Server Compact 3.5 for Windows Mobile Beta 2: The download contains the CAB files and DLL’s that are used to install SQL Server Compact 3.5 on the Windows Mobile Devices platform

3. SQL Server Compact 3.5 Server Tools Beta 2: Installs the Server Tools on the IIS server enabling merge replication and remote data access (RDA) with SQL Server 2005 and later.

4. SQL Server Compact 3.5 Books Online Beta 2: The download contains SQL Server Compact 3.5 Books Online and Read Me. SQL Server Compact 3.5 Books Online do not install with Visual Studio 2008 Beta 2. The Books Online are only available on the Web.

[Information care of Ambrish's post on the SSCE forum]

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

SQL Server CE Books

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

Q: Are there any books available on SQL Server Compact Edition?

A: Yes, there are a number of books available on developing for Windows Mobile (most of which include some discussion on SQL CE/Mobile). Specifically about SQL Server Compact Edition there is Bill Vaughn's book "Hitchhiker's Guide to Microsoft SQL Server 2005 Compact Edition" which is available as an ebook from Bill's website here.

MEDC 2007

Administrator — Tue, 13 Feb 2007 03:20:00 GMT

In case you haven't already penciled these dates into your diary here are the dates (all of them) for the Microsoft Mobile and Embedded DevCon 2007:

Please select your MEDC event from the list below.

Location	City	Date
US	Las Vegas	May 1-3
India	Bangalore	May 10-12
Australia	Sydney	May 16
Taiwan	Taipei	May 29
Korea	Seoul	May 30
Japan	Tokyo	June 6
Singapore	Singapore	June 8
China	Beijing	June 21
Europe	Berlin	June 25-26

For more information please visit the MEDC website at www.medc2007.com

BUG: Aggregation

Administrator — Sun, 11 Feb 2007 07:39:00 GMT

Q: I'm getting incorrect results when I run an aggregation query, such as the following:

SELECT Orders.OrderDate, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM Orders inner join OrderLines on Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderDate

A: The product team have publically acknowledged that this is a problem and that they are actively looking for a resolution. Please check out the full discussion on this issue on the MSDN Forum thread here.

MARS

Administrator — Sun, 11 Feb 2007 07:36:00 GMT

Q: Does SQL Server CE support Multiple Active Resultsets?

A: Yes (since SQL Server Mobile 3.0) - unlike SQL Server 2005 there is no flag that has to be set as this feature is always on. However, it doesn't support batch sql commands.

Security Overview

Administrator — Fri, 09 Feb 2007 12:33:00 GMT

Q: I don't understand the security relationship between sql server, IIS and sql server compact edition. Is there a good starting point for me to get a grip of how it all works.

A: One of the great features added into SQL Server Compact Edition is the addition of more documentation. In particular there is a series of security flowcharts (under Concepts -> Troubleshooting) that cover the sql server, windows and iis security models. SQL Server Compact Edition documentation can be installed locally from C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Docs\EN after installing the SDK.

Nested expressions

Administrator — Fri, 09 Feb 2007 12:23:00 GMT

Q: Can I perform nested aggregate query expression such as:
        select [Order ID], [Customer ID] from orders
        left outer join
        (
         select [Order ID], sum(Quantity * [Unit Price]) as Total
         from [Order Details]
         group by [Order ID]
        ) as details on orders.[Order ID]=details.[Order ID]

A: No, unfortunately as per error value 25516 (SSCE_M_QP_NO_AGG_IN_AGG) in the SQL Server CE Books Online (which you can install locally via the msi located at C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Docs when you install the SDK) - "Nested aggregate expressions are not allowed".

Missing System.Data

Administrator — Fri, 09 Feb 2007 11:36:00 GMT

Q: When I add a reference to System.Data.SqlServerCE.dll I get "Reference required to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing..."?

A: This error can be a result of either a missing or invalid reference to the System.Data assembly. In the unusual case that you don't already have a reference to System.Data (ie a missing reference) you can easily add this via the Add Reference dialog.

Although Sql Server CE works on both the desktop and the mobile device the engine is actually two different sets of assemblies (primarily because most desktops are an x86 architecture while most devices are ARM based). As such when you are adding a reference to System.Data.Sqlserverce.dll you need to make sure you add the correct version.

If you install just the Sql Server CE runtime (ie SQLServerCE31-EN.msi) you will see that the desktop assemblies are available at C:\Program Files\Microsoft SQL Server Compact Edition\v3.1. If you add the assembly at this location to a mobile device application you will see a series of error similar to the above. This is because the assembly was compiled against a different version of the System.Data assembly to what is included in the references section of your project. To overcome this issue you need to add a reference from the correct location that is installed as part of the Sql Server CE SDK (ie SSCE31SDK-ENU.msi) at C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin

Going Mobile

Administrator — Fri, 09 Feb 2007 09:00:00 GMT

Q: Does SQL Server Compact Edition still work on mobile devices?

A: Yes, although the catch is that the runtimes for the mobile devices are NOT in the main runtime installer (downloadable from here). Instead they are included in the SQL Server CE SDK which can be downloaded here

Synchronising with Access

Administrator — Fri, 09 Feb 2007 08:36:00 GMT

Q: Can I synchronise a SQL Server CE database with Access?

A: Yes, there is currently a CTP version available of the Access Database Synchronizer which can be downloaded here. The following are some of the points of interest:

It has been designed to sync with Access, not SQL Express - if you want to synchronise to SQL Server you either need to upgrade to a version that supports Merge replication or investigate using the Microsoft Sync Services (currently in CTP).
Synchronisation has to be done via an ActiveSync connection - this currently excludes scenarios that involve syncing across a network (eg wireless).