SQL Server CE FAQ

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

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.

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

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.

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.

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.

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.

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

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

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

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

Q: Where can I get more information about SQL Server Compact Edition or provide feedback to the product team?

A: The primary way to provide feedback to Microsoft is via the MSDN forums, in particular the SQL Server CE forum that can be found here.  Laxmi started a great thread which summarises a number of useful resources for getting started and working with SQL Server Compact Edition.