How to deploy SQL Server Compact Edition 4.0?

sql-server-2008 deployment sql-server-ce ado xcopy
Ian Boyd picture Ian Boyd · May 24, 2012 · Viewed 39.3k times · Source

How do i deploy Microsoft SQL Server Compact 4.0?

SQL Server Compact Edition (currently at version 4.0) is:

a free, embedded database that software developers can use for building Windows desktop applications. It has a small footprint and supports private deployment of its binaries within the application folder.

But how do you actually deploy it?

The problem is that you cannot use the ADO OLEdb provider unless it is registered. Registering an OLEdb provider has to be done as an administrator. That means that SQL Server Compact edition will fail with users who are not an administrator.

SQL Server Compact 4.0 comes with a redist_enu.txt file:

The listed .exe files each install its enclosed components to a specific location on the destination computer. This helps to ensure serviceability and technical support. The .dll files enclosed in these .exe files are also available separately in this redist.txt. However, distributions of these separate .dlls may result in issues of serviceability. For more details, please see http://go.microsoft.com/fwlink/?LinkId=94589

Private deployment detection via BreadCrumb: Private deployment of just the native stack and explicit loading of SQL Server Compact Assembly via Assembly.LoadFrom(), .local file, or the use of DLL/COM redirection strategies are not supported and may result in serviceability issues. For more information see http://support.microsoft.com/kb/835322 and http://msdn2.microsoft.com/en-us/library/aa375142.aspx

Microsoft SQL Server Compact 4.0

SSCERuntime_x86-ENU.exe
SSCERuntime_x86-DEU.exe
SSCERuntime_x86-FRA.exe
SSCERuntime_x86-JPN.exe
SSCERuntime_x86-RUS.exe
SSCERuntime_x86-ESN.exe
SSCERuntime_x86-ITA.exe
SSCERuntime_x86-KOR.exe
SSCERuntime_x86-CHT.exe
SSCERuntime_x86-CHS.exe
SSCERuntime_x64-ENU.exe
SSCERuntime_x64-DEU.exe
SSCERuntime_x64-FRA.exe
SSCERuntime_x64-JPN.exe
SSCERuntime_x64-RUS.exe
SSCERuntime_x64-ESN.exe
SSCERuntime_x64-ITA.exe
SSCERuntime_x64-KOR.exe
SSCERuntime_x64-CHT.exe
SSCERuntime_x64-CHS.exe
sqlcese40.dll
sqlceqp40.dll
sqlceoledb40.dll
sqlceca40.dll
sqlceme40.dll
sqlcecompact40.dll
sqlceer40en.dll
sqlceer40cn.dll/sqlceer40zh-CHS.dll
sqlceer40de.dll
sqlceer40es.dll
sqlceer40fr.dll
sqlceer40it.dll
sqlceer40ja.dll
sqlceer40ko.dll
sqlceer40tw.dll/sqlceer40zh-CHT.dll
sqlceer40ru.dll
System.Data.SqlServerCe.dll
System.Data.SqlServerCe.Entity.dll

but it doesn't give any information about how to redistribute SQL Server Compact 4.0.

Randomly spellunking around the undocumented Program Files folder i found 7 dlls:

C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\
      sqlceoledb40.dll
      sqlceqp40.dll
      sqlcese40.dll
      sqlceca40.dll
      sqlcecompact40.dll
      sqlceer40EN.dll
      sqlceme40.dll

Note: There are also some child folders with more dlls

i tried copying these 7 dll's to a folder, and tried to open an ADO Connection using the connection string:

Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source="store.sdf"

but it fails with 0x80004005 Unspecified error

i tried frobbing the widget, but it grobbed the frobber.

Answer

Ian Boyd picture Ian Boyd · May 29, 2012

i've created the solution.

SQL Server Compact Edition is comprised of 7 dlls:

  • sqlceme40.dll The undocumented, native, flat API library (The .net System.Data.SqlServerCe.dll assembly is a wrapper around this dll)
  • sqlceca40.dll A COM dll that implements Engine, Replication, Error and a few other COM objects
  • sqlceoledb40.dll A COM dll that implements an OLEdb provider for SSCE (allowing the use of ADO)
  • sqlcese40.dll unknown
  • sqlceqp40.dll unknown
  • sqlcecompact40.dll unknown
  • sqlceer40en.dll unknown

The problem with trying to simply ship these dlls is that two of them are COM objects. COM object dll's need to be registered, e.g.:

>regsvr32 sqlceca40.dll
>regsvr32 sqlceoledb40.dll

The problem is that registering a COM object requires administrative privileges (using a global solution to solve a local problem). This means that your users would

  • have to install your application (which you don't want to do)
  • requires your users to have administrative permissions (which you don't want to do)

Fortunately, starting in 2001 with Windows XP, Microsoft solved this COMmon problem: Registration-Free COM.

First, you will declare that your application has a "dependancy" on SQL Server Compact Edition 4.0. You do this by authoring an assembly manifest:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0"> 
    <assemblyIdentity 
        version="1.0.0.0"
        processorArchitecture="X86"
        name="client"
        type="win32"
    /> 

    <description>Hyperion Pro</description> 

    <!-- We have a dependancy on SQL Server CE 4.0 -->
    <dependency>
        <dependentAssembly>
            <assemblyIdentity
                type="win32"
                name="Microsoft.SQLSERVER.CE.4.0"
                version="4.0.0.0" processorArchitecture="x86"
            />
        </dependentAssembly>
    </dependency>

    <!-- We are high-dpi aware on Windows Vista -->
    <asmv3:application xmlns:asmv3="urn:schemas-microsoft-com:asm.v3">
        <asmv3:windowsSettings
            xmlns="http://schemas.microsoft.com/SMI/2005/WindowsSettings">
            <dpiAware>true</dpiAware>
        </asmv3:windowsSettings>
    </asmv3:application>

    <!-- We were designed and tested on Windows 7 -->
    <compatibility xmlns="urn:schemas-microsoft-com:compatibility.v1">
        <application>
            <!--The ID below indicates application support for Windows 7 -->
            <supportedOS Id="{35138b9a-5d96-4fbd-8e2d-a2440225f93a}"/>
            <!--The ID below indicates application support for Windows Vista -->
            <!--supportedOS Id="{e2011457-1546-43c5-a5fe-008deee3d3f0}"/-->
        </application>
    </compatibility>

    <!-- Disable file and registry virtualization -->
    <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
        <security>
            <requestedPrivileges>
                <requestedExecutionLevel level="asInvoker" uiAccess="false"/>
            </requestedPrivileges>
        </security>
    </trustInfo>
</assembly>

You can place this file beside your executable (as Hyperion.exe.manifest), or you can build it into your application as an RT_MANIFEST resource.

Notice that we have a dependancy against as assembly called Microsoft.SQLSERVER.CE.4.0. We create this assembly first by creating a directory called:

Microsoft.SQLSERVER.CE.4.0

When you deploy your application, you will place all 7 dll's that comprise this "assembly" into this Microsoft.SQLSERVER.CE.4.0 subfolder, along with a special .manifest file:

C:\
|---Users
   |---Ian
       |---AppData
           |---Local
               |---Hyperion Pro
                   |    Hyperion.exe
                   |    Hyperion.exe.manifest
                   |----Microsoft.SQLSERVER.CE.4.0
                            sqlceme40.dll
                            sqlceca40.dll
                            sqlceoledb40.dll
                            sqlcese40.dll
                            sqlceqp40.dll
                            sqlcecompact40.dll
                            sqlceer40en.dll
                            Microsoft.SQLSERVER.CE.4.0.manifest

In other words, the application folder contains your application, and the Microsoft.SQLSERVER.CE.4.0 folder:

 Directory of C:\Users\Ian\AppData\Local\Hyperion Pro

05/29/2012  09:23 AM         1,899,008 Hyperion.exe
05/28/2012  01:46 PM             1,587 Hyperion.exe.manifest
05/29/2012  09:27 AM    <DIR>          Microsoft.SQLSERVER.CE.4.0
           2 File(s)      1,900,675 bytes
           1 Dir(s)  20,851,503,104 bytes free

The next part of your task is to define the Microsoft.SQLSERVER.CE.4.0.manifest file. Registration-free COM allows a manifest file to declare all the COM objects and their clsid's. This took a lot of reverse engineering. But the assembly manifest for SQL Server Compact Edition 4.0 is:

Microsoft.SQLSERVER.CE.4.0.manifest:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">

<assemblyIdentity 
       type="win32"
       name="Microsoft.SQLSERVER.CE.4.0"
       processorArchitecture="x86"
       version="4.0.0.0" />

<!-- OLEDB Provider -->
<file name = "sqlceoledb40.dll">
    <comClass
            description = "Microsoft SQL Server Compact OLE DB Provider for Windows"
            clsid="{2006C53A-C915-41EA-BAA9-9EAB3A1FBF97}"
            threadingModel = "Both"
            progid = "Microsoft.SQLSERVER.CE.OLEDB.4.0" />
</file>

<!-- Native flat engine library -->
<file name="sqlceme40.dll" />

<!-- Engine and Replication COM object -->
<file name="sqlceca40.dll">
    <comClass description="Active SSCE Engine Object"
            clsid="{68D45319-3702-4837-9F8E-DA6845D82482}"
            threadingModel="Both"
            progid="SSCE.Engine.4.0" />
    <comClass description="SSCE Error Object"
            clsid="{36228F21-B5C7-4054-8DC2-47D3E236E8B5}"
            threadingModel="Both"
            progid="SSCE.Error.4.0" />
    <comClass description="SSCE Param Object"
            clsid="{0B3A7B75-A9B0-4580-9AA5-1A7DA47AD1CB}"
            threadingModel="Both"
            progid="SSCE.Param.4.0" />
    <comClass description="Active SSCE Replication Object"
            clsid="{11D5B2D4-26A4-44F5-A48B-0FAC3A919ED8}"
            threadingModel="Both"
            progid="SSCE.Replication.4.0" />
    <comClass description="Active SSCE remote data access Object"
            clsid="{58BC9AD6-BF11-40B3-9AB1-E3F2ED784C08}"
            threadingModel="Both"
            progid="SSCE.RemoteDataAccess.4.0" />

    <typelib tlbid="{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}"
            version="4.0" 
            helpdir=""/>
</file>

<comInterfaceExternalProxyStub 
    name="ISSCEEngine"
    iid="{10EC3E45-0870-4D7B-9A2D-F4F81B6B7FA2}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCEError"
    iid="{31155A3B-871D-407F-9F73-DEBFAC7EFBE3}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCERDA"
    iid="{4F04F79D-1FF1-4DCD-802B-3D51B9356C14}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCEParams"
    iid="{A78AFF90-049C-41EC-B1D8-665968AAC4A6}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCEParam"
    iid="{A9876C60-2667-44E5-89DB-E9A46ED392C0}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCEErrors"
    iid="{C40143CA-E9F9-4FF4-B8B4-CC02C064FC1B}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<comInterfaceExternalProxyStub 
    name="ISSCEMerge"
    iid="{C6EB397F-D585-428D-A4F4-454A1842CB47}"
    proxyStubClsid32="{00020424-0000-0000-C000-000000000046}"
    baseInterface="{00000000-0000-0000-C000-000000000046}"
    tlbid = "{CE4AACFA-3CFD-4028-B2D9-F272314F07C8}" />

<file name="sqlceqp40.dll" />
<file name="sqlcese40.dll" />
<file name="sqlcecompact40.dll" />
<file name="sqlceer40EN.dll" />

</assembly>

The a final gotcha is that, in the same way we have a dependancy on an assembly called Microsoft.SQLSERVER.CE.4.0, SQL Server Compact Edition 4.0 in turn has a dependancy on an assembly called Microsoft.VC90.CRT. Fortunately your install of SQLCE ships with a copy of this assembly:

|----Microsoft.VC90.CRT
    |    Microsoft.VC90.CRT.manifest 
    |    msvcr90.dll

This means the final directory structure is:

C:\
|---Users
   |---Ian
      |---AppData
         |---Local
            |---Hyperion Pro
               |    Hyperion.exe
               |    Hyperion.exe.manifest
               |----Microsoft.SQLSERVER.CE.4.0
                   |   Microsoft.SQLSERVER.CE.4.0.manifest
                   |   sqlceme40.dll
                   |   sqlceca40.dll
                   |   sqlceoledb40.dll
                   |   sqlcese40.dll
                   |   sqlceqp40.dll
                   |   sqlcecompact40.dll
                   |   sqlceer40en.dll
                   |---Microsoft.VC90.CRT
                      |   Microsoft.VC90.CRT.manifest
                      |   msvcr90.dll

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More