The most up-to-date version of this file is available online at the Microsoft Download Center.

The SQL Server documentation team welcomes your documentation feedback. We view and investigate all documentation issues but do not answer technical support questions here. For help with technical issues not related to documentation, see Getting Assistance with SQL Server Compact. If it is possible, submit feedback to us in English.

Contents

1.0 Introduction

Microsoft® SQL Server™ Compact 3.5 Service Pack 2 is a small footprint, in-process database engine that allows developers to build robust applications for Windows desktop computers and mobile devices.

1.1 Obtaining SQL Server Compact 3.5 SP2

SQL Server Compact 3.5 SP2 installs with SQL Server 2008 R2 and Visual Studio 2010, and is also available from the following Web downloads:

·         SQL Server Compact 3.5 SP2 (32-bit and 64-bit releases).

·         Visual Studio 2010. Note that Visual Studio 2010 also installs the 64-bit release of SQL Server Compact 3.5 SP2.

·         SQL Server 2008 R2. Note that SQL Server 2008 R2 only installs the 32-bit release of SQL Server Compact 3.5 SP2, and not the 64-bit release. The 64-bit release is available with the SQL Server Compact 3.5 SP2 download.

·         SQL Server Compact 3.5 SP2 for Devices. Note that the SQL Server Compact 3.5 SP2 for Devices Windows installer file (MSI) that contains the device runtime .cab files is not installed with Visual Studio 2010.

·         SQL Server Compact 3.5 SP2 Server.

1.2 SQL Server Compact 3.5 SP2 Books Online

SQL Server Compact 3.5 SP2 Books Online provides detailed information about the development, administration, and deployment of SQL Server Compact 3.5 SP2 on Windows desktop computers and Windows Mobile devices. SQL Server Compact 3.5 SP2 Books Online does not install with the Visual Studio 2010 documentation or with SQL Server 2008 R2 Books Online, and is only available as a Web download from the Microsoft Download Center.

After you install SQL Server Compact Books Online, it is available on the Start menu as a stand-alone Help collection. To access this collection from the SQL Server 2008 R2 documentation and to access F1 Help from SQL Server 2008 R2, you must add the collection to the SQL Server Combined Help Collection. For instructions on adding the collection, see Known Issues, later in this document.

1.3 Microsoft Synchronization Services for ADO.NET Version 1.0 SP1

Microsoft Synchronization Services for ADO.NET provides the ability to synchronize data from disparate sources over two-tier, n-tier, and service-based architectures.

To learn more about this technology, see the product documentation. The Synchronization Services components are available with SQL Server Compact 3.5 SP2 download.

[Top]

2.0 Supported Operating Systems and Platforms for SQL Server Compact 3.5 SP2

SQL Server Compact 3.5 SP2 supports the following operating systems.

Windows Desktop Computers

·         Microsoft Windows XP Professional SP3

·         Microsoft Windows XP Home Edition SP3

·         Microsoft Windows XP Media Center Edition 2004 SP3

·         Microsoft Windows XP Media Center Edition 2005

·         Microsoft Windows XP Tablet PC Edition SP3

·         Microsoft Windows XP Embedded SP3

·         Microsoft Windows XP Professional x64 SP3

·         Windows Embedded for Point of Service SP3

·         Microsoft Windows Server 2003, Standard Edition SP2

·         Microsoft Windows Server 2003, Enterprise Edition SP2

·         Microsoft Windows Server 2003, Datacenter Edition SP2

·         Microsoft Windows Server 2003 R2, Standard Edition

·         Microsoft Windows Server 2003 R2, Enterprise Edition

·         Microsoft Windows Server 2003 R2, Datacenter Edition

·         Microsoft Windows Server 2003 x64, Standard Edition

·         Microsoft Windows Server 2003 x64, Enterprise x64 Edition

·         Microsoft Windows Server 2003 x64, Datacenter Edition

·         Microsoft Windows Server 2003 R2 x64, Standard Edition

·         Microsoft Windows Server 2003 R2 x64, Enterprise Edition

·         Microsoft Windows Server 2003 R2 x64, Datacenter Edition

·         Windows Vista Home Basic SP2

·         Windows Vista Home Premium SP2

·         Windows Vista Business SP2

·         Windows Vista Enterprise SP2

·         Windows Vista Ultimate SP2

·         Windows Vista Starter Edition SP2

·         Windows Vista Home Basic x64 SP2

·         Windows Vista Home Premium x64 SP2

·         Windows Vista Business x64 SP2

·         Windows Vista Enterprise x64 SP2

·         Windows Vista Ultimate x64 SP2

·         Windows Server 2008 Standard Server SP2

·         Windows Server 2008 Standard Server SP2 (without Hyper-V)

·         Windows Server 2008 Enterprise SP2

·         Windows Server 2008 Enterprise SP2 (without Hyper-V)

·         Windows Server 2008 Data Center SP2

·         Windows Server 2008 Data Center SP2 (without Hyper-V)

·         Windows Server 2008 Web Edition SP2

·         Windows Server 2008 Standard Server SP2 x64

·         Windows Server 2008 Standard Server SP2 x64 (without Hyper-V)

·         Windows Server 2008 Enterprise SP2 x64

·         Windows Server 2008 Enterprise SP2 x64 (without Hyper-V)

·         Windows Server 2008 Data Center SP2 x64

·         Windows Server 2008 Data Center SP2 x64 (without Hyper-V)

·         Windows Server 2008 Web Edition SP2 x64

·         Windows 7 Starter

·         Windows 7 Starter x64

·         Windows 7 Home Premium

·         Windows 7 Home Premium x64

·         Windows 7 Professional

·         Windows 7 Professional x64

·         Windows 7 Ultimate

·         Windows 7 Ultimate x64

·         Windows Server 2008 R2 Foundation x64

·         Windows Server 2008 R2 Standard x64

·         Windows Server 2008 R2 Enterprise x64

·         Windows Server 2008 R2 Datacenter x64

·         Windows Web Server 2008 R2 x64

 

Windows Mobile Devices

·         Windows CE 4.2

·         Windows CE 5.0

·         Windows CE 6.0

·         Windows Mobile 2003 for Pocket PC

·         Windows Mobile 5.0

·         Windows Mobile 6.0

·         Windows Mobile 6.5

[Top]

3.0 Known Issues

3.1

SQL Server Compact 3.5 SP1 Books Online (BOL) and the Microsoft Synchronization Services for ADO.NET documentation are available as Web downloads only from the Microsoft Download Center and are not included with Visual Studio 2010 or SQL Server 2008 R2. To access these collections from the SQL Server 2008 R2 documentation and to access F1 Help from SQL Server 2008 R2, you must add these collections to the SQL Server Combined Help Collection. To do that, download SQL Server Compact 3.5 SP2 Books Online and the Microsoft Synchronization Services for ADO.NET documentation. Then, close all instances of the SQL Server Combined Help Collection and SQL Server 2008. When you re-open the Combined Help Collection, SQL Server Compact 3.5 SP2Books Online and Microsoft Synchronization Services for ADO.NET Books Online become available in the applications' table of contents, index, search, and F1 Help for code and the user interface.

3.2

The SQL Server Compact 3.5 SP2 for Devices Windows installer file does not install with Visual Studio 2010. The file is available as a Web download from the Microsoft Download Center.

3.3

You can upgrade from SQL Server Compact 3.5, SQL Server Compact 3.5 SP1, or SQL Server Compact 3.5 SP2 Beta to SQL Server Compact 3.5 SP2 by running the SQL Server Compact 3.5 SP2 MSI.

Uninstalling a CTP or beta version of Visual Studio 2010 or SQL Server 2008 R2 does not uninstall SQL Server Compact 3.5 SP2 Beta. You must remove pre-release installations of SQL Server Compact 3.5 SP2 manually before you install the release version of Visual Studio 2010 or SQL Server 2008 R2, as follows:

·        Uninstall the CTP or beta version of Visual Studio 2010.

·        On Windows Vista, Windows 7, Windows Server 2008, or Windows Server 2008 R2, click Start and then Control Panel, and then double-click Programs and Features. Uninstall all pre-release installations of SQL Server Compact 3.5 SP2 and SQL Server Compact 3.5 SP2 Server Tools.

·        On Windows XP or Windows Server 2003, click Start and then Control Panel, and then double-click Add or Remove Programs. Uninstall all pre-release installations of SQL Server Compact 3.5 SP2 and SQL Server Compact 3.5 SP2 Server Tools.

 

3.4

SQL Server Compact 3.5 SP2 does not use the Encrypt property of the SqlCeConnection class, and you should not use this property to encrypt database files.
 

Note:

The Encrypt property will be deprecated in a future release. This property is retained in SQL Server Compact 3.5 SP2 only for backward compatibility.

Use the Encryption Mode property of the SqlCeConnection class to encrypt SQL Server Compact 3.5 SP2 database files. The following examples show uses of the Encryption Mode property:

·        To create a new encrypted SQL Server Compact 3.5 SP2  database:
C#:

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;");

engine.CreateDatabase();

Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;")

engine.CreateDatabase()

·        To upgrade a SQL Server 2005 Compact Edition (version 3.1) or SQL Server 2005 Mobile Edition (version 3.0) database to an encrypted SQL Server Compact 3.5 SP2  database:
C#:

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;");

engine.Upgrade ("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;");

Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;")

engine.Upgrade("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;")

·        To change the encryption mode of an existing SQL Server Compact 3.5 SP2  database:
C#:

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;");

engine.Compact("Data Source=Northwind.sdf;encryption mode=ppc2003 compatibility;Password=<enterStrongPasswordHere>;");

Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;")

engine.Compact("Data Source=Northwind.sdf;encryption mode=ppc2003 compatibility;Password=<enterStrongPasswordHere>;")

·        To encrypt an unencrypted SQL Server Compact 3.5 SP2  database:
C#:

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf");

engine.Compact("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;");

Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;")

engine.Compact("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;")

 

3.5

When upgrading Visual Studio 2005 or Visual Studio 2008 SP1 to Visual Studio 2010, encrypted SQL Server Compact databases are not upgraded and ClickOnce applications must be re-published.

Database files (*.sdf) created by earlier versions of SQL Server Compact are not compatible with SQL Server Compact 3.5 SP2. Only unencrypted database files from earlier versions of SQL Server Compact are upgraded when a Visual Studio 2005 or Visual Studio 2008 project is upgraded to Visual Studio 2010.  Encrypted database files have to be manually upgraded. If the Visual Studio 2005 or Visual Studio 2008 project uses ClickOnce to publish applications, the application should be re-published after the project has been upgraded in Visual Studio 2010.

Encrypted database files can be manually upgraded by using the following steps:

1.  Click Data.

2.  Click Add New Data Source.

3.  Click Add Connection.

If an earlier version of the database file is opened, a message appears. If you click OK, the Upgrade to SQL Server Compact 3.5 Database dialog box appears.

 

Alternatively, the SqlCeEngine.Upgrade API can be used for programmatic scenarios, as shown in the following example:
C#:

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;");

engine.Upgrade ("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;");

Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;")

engine.Upgrade("Data Source=Northwind.sdf;encryption mode=platform default;Password=<enterStrongPasswordHere>;")

 

3.6

If the project uses ClickOnce to publish applications, the application should be re-published after it has been upgraded in Visual Studio 2010. When you re-publish the ClickOnce application in Visual Studio 2010, you might see a warning that a SQL Server 2005 Compact Edition prerequisite could not be found for bootstrapping. The warning should be ignored.

3.7

SQL Server Compact 3.5 SP2 data types for parameters like SqlDbType or DbType should be explicitly set.

If the data types for parameters such as SqlDbType or DbType are not explicitly set, an exception will be thrown. To resolve this issue, explicitly set the data type for parameters such as SqlDbType or DbType. This is critical in the case of BLOB data types (image and ntext). A code example is given below:


C#:

SqlCeEngine engine = new SqlCeEngine(connString);

engine.CreateDatabase();

engine.Dispose();

 

SqlCeConnection conn = new SqlCeConnection(connString);

conn.Open();

 

SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);";

cmd.ExecuteNonQuery();

 

cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";

SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);

SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);

paramName.Value = "Name1";

paramBlob.Value = "Name1".PadLeft(4001);

 

cmd.ExecuteNonQuery();

 Visual Basic:

Dim engine As SqlCeEngine = New SqlCeEngine(connString)

engine.CreateDatabase()

engine.Dispose()

 

Dim conn As SqlCeConnection = New SqlCeConnection(connString)

conn.Open()

 

Dim cmd As SqlCeCommand = conn.CreateCommand()

cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);"

cmd.ExecuteNonQuery()

 

cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);"

Dim paramName As SqlCeParameter

Dim paramBlob As SqlCeParameter

paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128)

paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText)

paramName.Value = "Name1"

paramBlob.Value = "Name1".PadLeft(4001)

 

cmd.ExecuteNonQuery()

 

3.8

A ClickOnce application that uses DbProviderFactory to load the SQL Server Compact 3.5 SP2 provider will not work in the following cases:

 

·        A computer already has SQL Server Compact 3.5 SP2 and .NET FX 2.0, 3.0, or 3.5 installed, and the ClickOnce application installs .NET FX 4 and SQL Server Compact 3.5 SP2 on the computer.

 

·        A computer already has SQL Server Compact 3.5 SP2 and .NET FX 4 and installed and the ClickOnce application installs .NET FX 2.0, 3.0, or 3.5 on the computer.

 

In these cases, the ClickOnce application will not work because SQL Server Compact is not reinstalled and the SQL Server Compact entries are not written to the machine.config file of the .NET FX version installed by the ClickOnce app. Because the SQL Server Compact entries are not in this machine.config file, the application using the DbProviderFactory method cannot load the SQL Server Compact provider and the application will fail.

 

This happens because SQL Server Compact is reinstalled only when the following registry key does not exist (otherwise, the reinstallation of SQL Server Compact is skipped, assuming that it is already installed correctly):

 

"HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5\GAC\System.Data.SqlServerCe.dll_<LOC>"

 

<LOC> is the language code, such as ENU for English.

 

This registry key is created when SQL Server Compact is installed on a machine that has .NET FX 2.0, 3.0, or 3.5 installed. When the ClickOnce application is installed, it installs .NET FX 4 and then tries to install SQL Server Compact. If, while installing Compact, it finds that this registry key is already created, it skips the reinstallation of SQL Server Compact. Thus SQL Server Compact is not repaired when .NET FX 4 is installed and does not make the required entries in the machine.config file of .NET FX 4. The issue also occurs if the application is compiled with .NET FX 2.0, 3.0, or 3.5 and .NET FX 4 is already installed on the machine.

 

The workaround is to repair the SQL Server Compact 3.5 SP2 installation from Add/Remove Programs. To repair the SQL Server Compact 3.5 SP2 installation, click Start and then Control Panel, and then double-click Programs and Features. Go to Microsoft SQL Server Compact 3.5 SP2, select it, right-click it, and then click Repair. The repair dialog box for SQL Server Compact 3.5 SP2 will launch. Follow the steps in the dialog box to complete the repair.  

 

3.9

When a new database file is created with SQL Server Compact 3.5 SP2 but change tracking (for Synchronization Services for ADO.NET version 1.0 SP1, Sync Framework version 1.0, or Sync Framework version 2.0) is enabled on this database file using a SQL Server Compact 3.5 SP1 runtime, there is an issue when SQL Server Compact 3.5 SP1 is upgraded to SQL Server Compact 3.5 SP2. In this case, the change tracking schema in the database file will not be upgraded and the context column will not be added to the tables that are being tracked for changes.

To prevent this issue, ensure that if a database is created with SQL Server Compact 3.5 SP2 change tracking is also enabled with SQL Server Compact 3.5 SP2.   

3.10

The SQL Server Compact 3.5 SP2 Query Tools Windows installer file (MSI) is uninstalled when SQL Server 2008 and SQL Server 2008 R2 are installed side-by-side and then SQL Server 2008 is uninstalled. Similarly, the SQL Server Compact 3.5 SP1 Query Tools MSI is uninstalled when SQL Server 2008 and SQL Server 2008 R2 are installed side-by-side and SQL Server 2008 R2 is uninstalled. Uninstalling the Query Tools MSI means that SQL Server Management Studio (SSMS) and SQL Server Integration Studio (SSIS) can no longer connect with SQL Server Compact.

To resolve this problem, install the SSCESqlWbTools <Language>.msi for the SQL Server Compact 3.5 SP1 Query Tools or the SQL Server Compact 3.5 SP2 Query Tools. The file is available in the Servers\Setup folder on the SQL Server 2008 or SQL Server 2008 R2 installation media.

Note: <Language> is a placeholder for the language of the Query Tools MSI. For example, ENU is for English, and JPN is for Japanese.

4.0 Additional Information

This section describes how to obtain assistance and provide feedback about SQL Server Compact.

4.1 Getting SQL Server Compact Assistance

For more information, see the SQL Server Compact Forum and the SQL Server Compact 3.5 Team Blog.

4.2 Providing Feedback on SQL Server Compact

To provide suggestions and bug reports about SQL Server Compact:

·         Make suggestions and file bug reports about the features or the user interface of SQL Server Compact at the MSDN product feedback Web site.

·         Send suggestions or report inaccuracies in the documentation by using the feedback functionality in SQL Server Compact Books Online.

[Top]