SQL Server Compact Edition Runtime (SQLServerCE31-EN.msi)
SQL Server Compact Edition contains the runtime bits that can be used to develop applications and to deploy applications on desktop computers and on Tablet PCs. SQL Server Compact Edition is a powerful yet lightweight relational database engine. SQL Server Compact Edition supports familiar SQL syntax. Additionally, SQL Server Compact Edition provides a development model and an API that are consistent with SQL Server. Therefore, you can more easily develop desktop applications by using the RTW version of SQL Server Compact Edition than by using SQL Server Mobile. SQL Server Compact Edition includes new features such as
|DataDirectory| substitution string support and ClickOnce support so that deployment is easy. The SQL Server Compact Edition Runtime binary includes the following DLLs:
- Sqlceca30.dll
- Sqlcecompact30.dll
- Sqlceer30en.dll
- Sqlceme30.dll
- Sqlceoledb30.dll
- Sqlceqp30.dll
- Sqlcese30.dll
- System.Data.SqlServerCe.dll
The Microsoft Windows Installer package for the SQL Server Compact Edition Runtime (SQLServerCE31-EN.msi) is available on the Web. The Windows Installer package is always installed in a fixed location to handle future serviceability of SQL Server Compact Edition. When you install SQL Server Compact Edition, the provider (System.Data.SqlServerCe.dll) is installed in the global assembly cache (GAC). Additionally, the OLE DB provider (Sqlceoledb30.dll) is registered.
To register the OLE DB provider, you must have Administrator permissions. Therefore, you cannot install SQL Server Compact Edition unless you have Administrator permissions. The Windows Installer package installs these binaries in the "%ProgramFiles%\Microsoft SQL Server Compact Edition\v3.1" folder. After you install SQL Server Compact Edition, a
Microsoft SQL Server 2005 Compact Edition entry is added in
Add or Remove Programs.
To install the localized version (SQLServerCE31-
[LN].msi), you must install the English version (SQLServerCE31-EN.msi) first, and then install the localized version.
Note [LN] represents the localized language.
SQL Server Compact Edition Tools for Visual Studio 2005 SP1 (SSCE31VSTools-ENU.exe)
SQL Server Compact Edition Tools for Visual Studio 2005 SP1 updates the Microsoft Visual Studio 2005 SP1 smart device development components by using the design time environment for SQL Server Compact Edition.
To install the localized version (SSCE31VSTools-
[LN].exe), you must install the English version (SSCE31VSTools-ENU.exe) first, and then install the localized version.
SQL Server Compact Edition Developer SDK (SSCE31SDK-ENU.msi)
SQL Server Compact Edition Developer SDK contains the following files for developing applications by using SQL Server Compact Edition as a database store for the applications:
- The .cab files for installing SQL Server Compact Edition on mobile devices
- The .msi file for installing SQL Server Compact Edition on desktop computers and on Tablet PCs
- The header files that are required for developing a native SQL Server Compact Edition application
- The .msi file for installing the SQL Server Compact Edition Help files
To download SQL Server Compact Edition Developer SDK, visit the following Microsoft Web site:
The English version of SQL Server Compact Edition Developer SDK contains only the English version of SQL Server Compact Edition Runtime. The localized version of SQL Server Compact Edition Developer SDK contains the English version of SQL Server Compact Edition Runtime and the localized version SQL Server Compact Edition Runtime. After you install SQL Server Compact Edition Developer SDK, the installer package for SQL Server Compact Edition Runtime is located in the following folder:
%programfiles%\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop
To install the localized version of SQL Server Compact Edition Runtime from the localized version of SQL Server 2005 Compact Edition Developer SDK, follow these steps:
- Open the following folder:
%programfiles%\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop
- Install the English version of SQL Server Compact Edition Runtime (SQLServerCE31-EN.msi).
- Install the localized version of SQL Server Compact Edition Runtime (SQLServerCE31-[LN].msi).
Side-by-side installation together with Visual Studio 2005 or SQL Server 2005
SQL Server Mobile 3.0 requires Visual Studio 2005 or SQL Server 2005 to be installed on the computer. However, SQL Server Compact Edition does not have this requirement. Therefore, users can develop and deploy SQL Server Compact Edition applications without having SQL Server 2005 or Visual Studio 2005 installed. However, this change may cause some behavior changes on a computer that has SQL Server Compact Edition installed together with Visual Studio 2005 or with SQL Server 2005. Users experience the following behavior in a side-by-side installation together with Visual Studio 2005 or with SQL Server 2005.
Visual Studio 2005 behavior
Visual Studio 2005 together with SQL Server Compact Edition
- Management dialog boxes in Visual Studio 2005 display SQL Server Mobile 3.0-related elements.
- Visual Studio 2005 starts by using the SQL Server Compact Edition Runtime. This behavior occurs because SQL Server Compact Edition installs the provider in the GAC. The SQL Server Mobile 3.0 Runtime is present in the Visual Studio 2005 installation location. However, the GAC takes precedence over the SQL Server Mobile 3.0 Runtime. Therefore, Visual Studio 2005 always uses the SQL Server Compact Edition Runtime at startup.
Visual Studio 2005 SP1 together with SQL Server Compact Edition
- The user interface displays SQL Server Compact Edition-related elements.
- Visual Studio 2005 SP1 starts by using the SQL Server Compact Edition Runtime.
SQL Server 2005 behavior
SQL Server 2005 or SQL Server 2005 SP1 together with SQL Server Compact Edition
- All user interfaces display SQL Server Mobile 3.0-related elements.
- SQL Server 2005 starts by using the SQL Server Compact Edition Runtime.
SQL Server 2005 SP2 together with SQL Server Compact Edition
- The SQL Server 2005 SP2 user interface displays SQL Server Compact Edition-related elements.
- SQL Server 2005 SP2 starts by using the SQL Server Compact Edition Runtime.
New features
SQL Server Compact Edition includes some features that are not included in SQL Server Mobile 3.0. These features include the following:
- |DataDirectory| substitution string support
- ClickOnce support
|DataDirectory| substitution string support
|DataDirectory| (enclosed in pipe symbols) is a substitution string that indicates the database path. Therefore, you do not have to include the full path in the code. When you include the full path in the code, you may experience problems because the full database path can be serialized in different locations. The
|DataDirectory| substitution string also makes it easy to share a project and to deploy an application.
For example, if you include the full path in the code, the application can have the following connection string.
Data Source= c:\program files\MyApp\Mydb.sdf
If you use the
|DataDirectory| substitution string, the application can have the following connection string.
Data Source = |DataDirectory|\Mydb.sdf
To set the
DataDirectory property, call the
AppDomain.SetData method. If you do not set the
DataDirectory property, the following default rules are applied to access the database folder:
- For applications that are put in a folder on the user's computer, the database folder uses the application folder.
- For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.
Note The .NET Compact Framework does not support the
AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the
AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.
ClickOnce support
ClickOnce is a new software installation technology that SQL Server Compact Edition supports. ClickOnce simplifies the deployment of a Windows-based application to a Web server or to a network file share. Administrators can deploy or update an application by updating the files on a server. Administrators do not have to individually update every client.
Visual Studio provides full support for publishing and updating applications that are deployed by using ClickOnce. ClickOnce support is available for projects that are created by using Microsoft Visual Basic, Microsoft Visual C#, and Microsoft Visual J#. However, ClickOnce support is not available for projects that are created by using Microsoft Visual C++.
You can download SQL Server Compact Edition Tools for Visual Studio 2005 SP1 and install it on Visual Studio 2005 SP1. This installs the integrated support for ClickOnce and installs the required ClickOnce files in the correct directory.
How to integrate ClickOnce with SQL Server Compact Edition
Note The descriptions of the files and the folders in this section use the English version of SQL Server Compact Edition. If you install another language version of SQL Server Compact Edition, the locale name in the file names and in the folder names may differ. For example, the locale name may be "Zh" instead of "EN."
To integrate ClickOnce with SQL Server Compact Edition, download SQL Server Compact Edition Tools for Visual Studio 2005 SP1 and install it on Visual Studio 2005 SP1. This installs the integrated support for ClickOnce. The required ClickOnce files are also installed in the appropriate directory. The following table describes the ClickOnce files.
| File name |
Description |
| Product.xml |
Defines the base package |
| Package.xml |
Defines any localization-specific information or functionality to be added to the base package |
| SqlCE31eula-EN.txt |
Contains the Microsoft Software License Terms |
The following table describes the installation directories of the ClickOnce files.
| File name |
The folder in which files are installed |
| Product.xml |
%Program Files%\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SQL Server Compact Edition\ |
| Package.xml |
%Program Files%\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SQL Server Compact Edition\en\ |
| SqlCE31eula-EN.txt |
%Program Files%\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SQL Server Compact Edition\en\ |
| SQLServerCE31-EN.msi |
%Program Files%\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SQL Server Compact Edition\en\ |
If the application that you build to include the ClickOnce technology has SQL Server Compact Edition as a prerequisite, ClickOnce uses the SQLServerCE31-EN.msi file that is available in the local folder. If the local copy is not available, ClickOnce downloads the SQLServerCE31-EN.msi file from the Microsoft Download Center during the installation process.
Performance improvements
In SQL Server Compact Edition 3.1, some default values for input parameters are changed for desktop computers to improve the performance. However, these default values remain unchanged for mobile devices for performance reasons. The following table shows the old default values and the new default values for both mobile devices and desktop computers.
| Input parameter |
Old default value for mobile devices |
New default value for mobile devices |
Old default value for desktop computers |
New default value for desktop computers |
| Max Buffer Size |
640 KB |
640 KB |
640 KB |
4096 KB |
| Lock Time Out |
2000 ms |
2000 ms |
2000 ms |
5000 ms |
| Database Size |
128 MB |
128 MB |
128 MB |
256 MB |
In addition to the changes in the default values, many changes are made in the SQL Server Compact Edition engine. These changes improve the performance of SQL Server Compact Edition on desktop computers.
Note If you receive error messages that are related to these input parameters in SQL Server Compact Edition 3.1, the error messages still display the old values for these input parameters. This problem occurs because the error messages are not updated to reflect the new values in SQL Server Compact Edition 3.1. This problem will be fixed in the next release of SQL Server Compact Edition.
Known issues for SQL Server Compact Edition 3.1 on Windows Vista
The SQL Server Compact Edition Runtime is supported on Windows Vista. The database functionality is thoroughly tested on Windows Vista. However, there are some known functional issues in SQL Server Compact Edition 3.1 on Windows Vista. The following sections describe the functional issues and the suggested workarounds.
To maintain backward compatibility, the database encryption feature of SQL Server 2005 Compact Edition uses the same encryption algorithms as SQL Server 2005 Mobile Edition. Those algorithms are now somewhat outdated and do not provide a level of security that satisfies the standard Microsoft product policies. We recommend that you use another way to help secure the data where you can, for example, Encrypting File System.
Issue 1
When you use the Configure Web Synchronization Wizard to configure Microsoft Internet Information Services (IIS) 7.0 on Windows Vista, you receive the following error message:
IIS not installed
Then, the Configure Web Synchronization Wizard fails.
Note The Configure Web Synchronization Wizard sets various parameters in IIS to enable the synchronization between SQL Server Compact Edition and SQL Server.
Workaround for issue 1
To work around this issue, follow these steps:
- Install all the components of IIS 6.0 Management Compatibility before you use the Configure Web Synchronization Wizard.
Note You can install these components in Programs and Features in Control Panel.
- Open a Command Prompt window that has elevated privileges, and then run the Configure Web Synchronization Wizard at the command prompt.
Issue 2
When you run SQL Server Compact Edition Server Tools (Sqlce30setupen.msi) Setup under Least Privilege to User Accounts (LUA) mode on Windows Vista, you receive the following error message:
SQL Server Replication Components not found
This issue occurs even if the SQL Server replication components are present. When you run the Sqlce30setupen.msi file, you receive a message that asks you to use elevated privileges. However, even after you click
Yes in the message, you still experience this issue.
Workaround for issue 2
Open a Command Prompt window that has elevated privileges, and then run the Sqlce30setupen.msi file.
Issue 3
When you run SQL Server Compact Edition Server Tools (Sqlce30setupen.msi) Setup under silent mode on Windows Vista, the installation fails.
This issue occurs when you specify the default user setting not to use elevated privileges. This issue occurs because the Setup program does not prompt you to use elevated privileges. Instead, the Setup program uses the default user setting for elevated privileges.
Workaround for issue 3
Specify the default user setting to use elevated privileges before you run SQL Server Compact Edition Server Tools.
Issue 4
Consider the following scenario. You have a computer that is running Visual Studio 2005. You upgrade the operating system on this computer to Windows Vista. In this scenario, the SQL Server Compact Edition 3.1 integration with Visual Studio 2005 is broken.
SQL Server Compact Edition 3.1 contains the SQL Server Compact Edition Tools for Visual Studio 2005 SP1 package. This package installs the components to integrate SQL Server Compact Edition 3.1 with Visual Studio 2005. This package also adds some registry entries that Visual Studio 2005 uses. After you upgrade the operating system to Windows Vista, the integration is broken.
Workaround for issue 4
To work around this issue, follow these steps:
- Uninstall the Smart Device Programmability component for Visual Studio 2005.
- If you already installed SQL Server Compact Edition Tools for Visual Studio 2005 SP1, uninstall SQL Server Compact Edition Tools for Visual Studio 2005 SP1.
- Install the Smart Device Programmability component from the Visual Studio 2005 installation CD.
- Reinstall SQL Server Compact Edition Tools for Visual Studio 2005 SP1.
Issue 5
Consider the following scenario. You have a computer that is running Windows Server 2008. You install SQL Server 2005 Service Pack 2 on this computer. In the
Connect Database dialog box in SQL Server Management Studio, you create a new SQL Server Compact Edition database. In this scenario, you notice that the database is created in the %WINDIR%\SYSTEM32 folder.
Workaround for issue 5
Specify a path together with the file name when you create a new SQL Server Compact Edition database in SQL Server Management Studio.
Other known issues for SQL Server Compact Edition 3.1
Issue 1
Consider the following scenario. You publish a localized SQL Server Compact Edition 3.1 application by using ClickOnce. Then, you deploy this application to another computer. In this scenario, the localized version of the SQL Server Compact Edition Runtime is not installed.
Workaround for issue 1
To work around this issue, create your own package .xml file for ClickOnce. Do this to make sure that the localized version of the SQL Server Compact Edition Runtime is installed when you deploy the application.
For more information about how to add prerequisites to an application, visit the following Microsoft Developer Network Web site (MSDN):
Issue 2
Consider the following scenario. You install the English version of the SQL Server Compact Edition Runtime. Then, you install a localized version of the SQL Server Compact Edition Runtime on the same computer. In this scenario, you notice that the following entry is deleted from the Machine.config file when you uninstall the SQL Server Compact Edition Runtime:
SQL Server Compact Edition Data Provider
Note The Machine.config file is in the following folder:
Microsoft.NET\Framework\v2.0.50727\CONFIG%WinDir
This issue may cause problems in provider-agnostic applications.
Workaround for issue 2
To work around this issue, use one of the following methods:
- Create a Config.xml file that is specific to an application.
- If you want to uninstall the localized version of the SQL Server Compact Edition Runtime, uninstall the English version of the SQL Server Compact Edition Runtime first.
- Manually add the following information to the DbProviderFactories section in the Machine.config file.
<add name="SQL Server Compact Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
Issue 3
You cannot install the SQL Server Compact Edition Tools for Visual Studio 2005 SP1 side by side for different language versions on the same computer.
Note You can install Visual Studio 2005 SP1 side by side for different language versions on the same computer.
Issue 4
In SQL Server Compact Edition 3.1, you cannot use the
sp_repladdcolumn stored procedure to perform the following operations in one SQL statement:
- Add a new column to a publisher.
- Add a foreign key constraint to the new column. The foreign key references a column of an existing table.
For example, you cannot run the following SQL statement in SQL Server Compact Edition 3.1.
Northwind..sp_repladdcolumn @source_object='Customers' , @column='ColNew' , @typetext='int constraint FK_Foo_Customer foreign key references Products ( ProductID )' , @publication_to_add='PubName')
Workaround for issue 4
Create two SQL statements to perform the operations separately.
For example, create the following SQL statements to perform the operations that you want.
EXEC Northwind..sp_repladdcolumn @source_object='Customers' , @column='ColNew' , @typetext='int' , @publication_to_add='PubName';
Alter table Customers add constraint FK_Foo_Customer foreign key (ColNew) references Products ( ProductID )
After you run the SQL statements, regenerate the snapshot, and then reinitialize the subscriptions.
Software updates
SQL Server Compact Edition 3.1 resolves many bugs that occur in earlier versions of SQL Server Compact Edition. The following list describes some of the bugs that SQL Server Compact Edition 3.1 resolves:
- There is a desktop restriction in earlier versions of SQL Server Compact Edition. Therefore, you must install Visual Studio 2005 or SQL Server 2005 before you install SQL Server Compact Edition.
This problem is resolved in SQL Server Compact Edition 3.1. If you use SQL Server Compact Edition 3.1, you can run SQL Server Compact Edition independently of Visual Studio 2005 or of SQL Server 2005.
- When you specify an alias for a NULL column in a Select statement, earlier versions of SQL Server Compact Edition do not display all the aliases that you specify for the column names in the output. Instead, earlier versions of SQL Server Compact Edition display the following column names:
column#<OrdinalNumber>
For example, you have the following Select statement.
Select c1 as Col1, NULL as Col2 from Table1
When you run this statement in earlier versions of SQL Server Compact Edition, you see the following column names in the output:
column#0, column#1
- Consider the following scenario. You specify the ON DELETE CASCADE constraint on a table in earlier versions of SQL Server Compact Edition. The table has only one row. Then, you delete the row in the table. When you delete the row, an index scan occurs. In this scenario, SQL Server Compact Edition frees the data page that contains the row. Additionally, if the reference count of the buffer or of the frame that holds the data page is zero, SQL Server Compact Edition marks the buffer or the frame as free.
- When you run earlier versions of SQL Server Compact Edition, you may receive the following error message:
Attempted to read or write protected memory
This problem occurs if the following conditions are true:
- SQL Server Compact Edition tries to write data to the right node of a binary tree (btree).
- The binary tree is full.
Note When SQL Server Compact Edition writes data to a node of a binary tree, SQL Server Compact Edition splits the node if the binary tree is full. SQL Server Compact Edition uses one of the following methods to split the node:
- Normal split
- Asc split
- Desc split
- In earlier versions of SQL Server Compact Edition, when you use a scrollable cursor to retrieve data from a binary large object (BLOB) column, a SSCE_M_COLUMNORDINALNOTFOUND error may occur.
This problem occurs because scrollable cursors handle binary large object data differently than forward-only cursors handle binary large object data. If you use scrollable cursors, the binary large object column ordinal may differ from the base table ordinal. However, both scrollable cursors and forward-only cursors use the base table ordinal to retrieve data from a binary large object column.