x First time here? Check out the FAQ

Come work for Umbraco - The Umbraco HQ are hiring Project managers, .NET developers and DevOps people!

SQL Server CE 4 known issues

    Umbraco 4.6 will feature support for SQL Server CE 4 which is an embedded version of SQL Server that works on shared hosting and in medium trust. There are certain differences in supported features between the "real" SQL Server and the CE version which can cause issues in both the core of Umbraco and in 3rd party packages. We hope to find all the issues in the core of Umbraco for the final release of 4.6 and we'll use this document to describe the things we've found and what we've done to fix the issues. This is meant as an inspiration for 3rd party package developers to update their packages if needed.

    Issues in SQL Statements

    IF statements are NOT supported
    For instance when Umbraco Courier 1.2 installs it'll use the following SQL to update permissions which will fail:

    "if exists(select id from umbracoUserType where id = 1 and not userTypeDefaultPermissions like '%^') BEGIN update umbracoUserType set userTypeDefaultPermissions = userTypeDefaultPermissions + '^' where id = 1 END"

    (still working on a work around)

    Nested queries are NOT supported

    For instance this optimized call is used to fetch documents for the tree view and will fail because of nested select statements:

     Select 
                     (select count(id) from umbracoNode where parentId = @id) as Children,
                     (select Count(published) as tmp from cmsDocument where published = 1 And nodeId = @id) as Published,
                     cmsContentVersion.VersionId,
                        cmsContentVersion.versionDate,

    ...

    Workaround was to move the nested select into an left outer join and use CASE statements in the select:

    Select 
    CASE WHEN (childrenTable.total>0) THEN childrenTable.total ELSE 0 END as Children,
    CASE WHEN (publishedTable.publishedTotal>0) THEN publishedTable.publishedTotal ELSE 0 END as Published,
    cmsContentVersion.VersionId,
    cmsContentVersion.versionDate,
    ...

    When using TOP queries you'll need to use TOP as a method call

    This will fail:

    SELECT top 10 * from umbracoNode

    Workaround is to wrap the number of rows in parentheses:

    SELECT top(10) * from umbracoNode

     

    Distinct in count is not supported

    For instance:

    SELECT count(distinct umbracoNode.id) from ....

     

    (work around is simply not to use distinct cals with the count method)

    Issues in working with SQL CE4 data in .NET

    When using a DataReader, the cursor can't re-read

    A typical pattern when reading data which would fail because the cursor in SQL CE can only move forward could be something like this:

    using(IRecordsReader dr = SqlHelper.ExecuteReader("Select alias,design,master from cmsTemplate where nodeId = " + this.Id))
    {
                if (dr.HasRecords)
                {
                    dr.Read();

    The HasRecords property will read the first row which means that in order to call the Read() method the cursor will need to move back which isn't supported in SQL CE 4. Workaround is a pattern where you assign the bool value of the Read() method and use that to check for data (it'll return false if no data has been read):

    using(IRecordsReader dr = SqlHelper.ExecuteReader("Select alias,design,master from cmsTemplate where nodeId = " + this.Id))
    {
                bool hasRows = dr.Read();
                if (hasRows)
                {

    This is also important when reading data using a while loop for instance