An introduction to SQL Server 2005 Compact Edition

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) is the rebranding of Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile 3.0) without the desktop restriction. SQL Server Mobile 3.0 was released in 2005 with Microsoft Visual Studio 2005 and with SQL Server 2005. SQL Server Mobile 3.0 is supported on devices and on Tablet PCs. SQL Server Mobile 3.0 is also partially supported on desktop computers.

To use SQL Server Mobile 3.0 on desktop computers, either Visual Studio 2005 or SQL Server 2005 must be installed. However, with SQL Server Compact Edition, this restriction has been removed. With SQL Server Compact Edition, you can develop and deploy applications independent of SQL Server 2005 and of Visual Studio 2005.

The SQL Server Compact Edition Community Technology Preview (CTP) was released on June 12, 2006. SQL Server Compact Edition Release Candidate 1 (RC1) was released on November 7, 2006. Both the CTP version and the RC1 version of SQL Server Compact Edition were only for runtime binaries. The Release to Web (RTW) version of SQL Server Compact Edition for all packages was released on January 15, 2007. The version of SQL Server Compact Edition RTW is 3.1. The following packages were released together with the RTW version of SQL Server Compact Edition:

• Microsoft SQL Server 2005 Compact Edition Runtime
• Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005 Service Pack 1 (SP1)
• Microsoft SQL Server 2005 Compact Edition Developer Software Development Kit (SDK)
• Microsoft SQL Server 2005 Compact Edition Server Tools
• Microsoft SQL Server 2005 Compact Edition Books Online

SQL Server Compact Edition for all packages is released in the following languages:

• German
• English
• Spanish
• French
• Italian
• Japanese
• Korean
• Chinese Traditional
• Chinese Simplified
• Russian

SQL Server Compact Edition will also be included with SQL Server in future service pack releases. SQL Server 2005 Service Pack 2 (SP2) will be released in the first quarter of 2007. The following are the high-level highlights for how Microsoft will service SQL Server Compact Edition:

• SQL Server Compact Edition is available on the Web and can be downloaded and installed.
• SQL Server Compact Edition will be integrated into Microsoft Update in the first quarter of 2007.
• SQL Server Compact Edition will be installed as part of the SQL Server 2005 SP2 installation.

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.

To download the SQL Server Compact Edition Runtime, visit the following Microsoft Web site: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 download SQL Server Compact Edition Tools for Visual Studio 2005 SP1, visit the following Microsoft Web site: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: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:

1. Open the following folder:
   %programfiles%\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop
2. Install the English version of SQL Server Compact Edition Runtime (SQLServerCE31-EN.msi).
3. Install the localized version of SQL Server Compact Edition Runtime (SQLServerCE31-[LN].msi).

SQL Server Compact Edition Server Tools (Sqlce30setupen.msi)

To download SQL Server Compact Edition Server Tools, visit the following Microsoft Web site: Note To install the localized version (Sqlce30setup[LN].msi), you do not have to install the English version (Sqlce30setupen.msi) first.

SQL Server Compact Edition Books Online (SSCE31BOL-EN.msi)

SQL Server Compact Edition Books Online installs the documentation for SQL Server Compact Edition.

To download SQL Server Compact Edition Books Online, visit the following Microsoft Web site:Note To install the localized version (SSCE31BOL-[LN].msi), you do not have to install the English version (SSCE31BOL-EN.msi) first.

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. If you use the |DataDirectory| substitution string, the application can have the following connection string. 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. The following table describes the installation directories of the ClickOnce files.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. 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: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:

1. 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.
2. 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: 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:

1. Uninstall the Smart Device Programmability component for Visual Studio 2005.
2. 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.
3. Install the Smart Device Programmability component from the Visual Studio 2005 installation CD.
4. 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:Note The Machine.config file is in the following folder: 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.

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