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?
- Microsoft says it can be deployed within the application folder (good) and supports xcopy deployment.
- Microsoft also says it cannot be deployed within the application folder (bad), and does not support xcopy deployment.
- some people have examples of it using simple xcopy deployment, but they don't go into the details required to complete the deployment
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
Best Answer
i've created the solution.
SQL Server Compact Edition is comprised of 7 dlls:
sqlceme40.dll
The undocumented, native, flat API library (The .netSystem.Data.SqlServerCe.dll
assembly is a wrapper around this dll)sqlceca40.dll
A COM dll that implementsEngine
,Replication
,Error
and a few other COM objectssqlceoledb40.dll
A COM dll that implements an OLEdb provider for SSCE (allowing the use of ADO)sqlcese40.dll
unknownsqlceqp40.dll
unknownsqlcecompact40.dll
unknownsqlceer40en.dll
unknownThe 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.:
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
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:
You can place this file beside your executable (as
Hyperion.exe.manifest
), or you can build it into your application as anRT_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: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:In other words, the application folder contains your application, and the Microsoft.SQLSERVER.CE.4.0 folder:
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:
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 calledMicrosoft.VC90.CRT
. Fortunately your install of SQLCE ships with a copy of this assembly:This means the final directory structure is: