02/27/2011 17:57:46

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 4.0 is a small footprint, in-process database engine that allows developers to build robust applications for Windows® desktop computers and for starter ASP.NET websites.

1.1 Obtaining SQL Server Compact 4.0

SQL Server Compact 4.0 is available from the following Web download:

Important

The installation behavior of SQL Server Compact 4.0 on 32-bit and 64-bit operating systems has changed as compared to the previous versions of SQL Server Compact. The installation of SQL Server Compact 4.0 on 32-bit and 64-bit operating systems is as given below:

Version

Operating System

Result

32-bit, SQL Server Compact 4.0

32-bit

Successful

64-bit, SQL Server Compact 4.0

64-bit

Successful

32-bit, SQL Server Compact 4.0

64-bit

Error

64-bit, SQL Server Compact 4.0

32-bit

Error

[Return to Contents]

1.2 SQL Server Compact 4.0 Books Online

2.0 Supported Operating Systems and Platforms for SQL Server Compact 4.0

SQL Server Compact 4.0 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

.NET Framework

3.0 Known Issues

3.1 SQL Server Compact 4.0 needs the Microsoft Visual C++ 2008 runtime libraries to be installed on the system or to be privately deployed along with SQL Server Compact 4.0 binaries in the application folders

3.2 Setup errors are seen while installing Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 on a Computer that already has Visual Web Developer 2010 Express and Visual C++ Express 2010 SP1 installed

The Microsoft Visual Studio® 2010 SP1 Tools for SQL Server Compact 4.0 needs prior installation of Visual Studio SP1. If Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 is installed on a system which already has Microsoft Visual Web Developer® 2010 Express and Microsoft Visual C++ Express 2010 SP1 installed together, it will result in the following error:

Error 25541. Failed to open XML file C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe.config, system error: -2147024786

To avoid this error, follow the steps below for installation:

  1. On a x64-bit Windows 7 or Windows 2008 Enterprise computer, install Visual Web Developer® 2010 Express. Verify that the registry key [HKEY_LOCAL_MACHINE\ Software\Microsoft\VisualStudio\10.0\Setup\VS] is not present.

  2. Install Visual C++ Express 2010 and check that registry key [HKEY_LOCAL_MACHINE\ Software\Microsoft\VisualStudio\10.0\Setup\VS] has been created.

  3. Install Visual Studio 2010 Service Pack 1.

  4. Go to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\SP] registry key and change its value to "0".

  5. Install Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0. It will not throw any error.

  6. After installation is complete, go to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\SP] registry key and change its value to "1".

3.3 Installation rolls back when installing SQL Server Compact 4.0 from a network location with the logging turned on

When SQL Server Compact executable is run from a network location and logging is turned on, then by default the location of log file is the network location. If you do not have write permissions at the network location, then installation will roll back. You should provide the path for saving the log file at a location where you have permission to write as well.

Important

If you try to run this operation at the network location: <network location>\SSCERuntime-ENU.exe /i /l ssce40log.txt, the command results in an error:

Error opening installation log file. Verify that the specified log file location exists and is writable.

To correct this issue, add the location to save log file, where you have Write permissions:

<network location>\SSCERuntime-ENU.exe /i /l <location>\ssce40log.txt

3.4 Uninstalling Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 when there are open instances of Visual Studio 2010 SP1, results in the SQL Server Compact 4.0 Local Database option still showing in the New Items dialog

All the instances of the Visual Studio 2010 SP1 development environment should be closed completely while uninstalling the Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0.

If the Visual Studio development environment is left open during uninstall, then even after the uninstallation is complete and you try to add a new item in a new project, the Visual Studio will show an option to select SQL Server Compact 4.0 Local Database.

3.5 Using SQL Server Compact 4.0 with .NET Framework 4 results in higher performance when working with large amounts of data

System.Data.SqlServerCe.dll is optimized to work with .NET Framework 4, especially while accessing a large amount of data in a result set. You might experience an enhanced performance with .NET Framework 4, as compared to using SQL Server Compact with .NET Framework 3.5 SP1.

3.6 The required entries to machine.config and GAC are not made when SQL Server Compact is installed on a Computer that does not have .NET Framework 3.5 or .NET Framework 4 installed

If the SQL Server Compact Runtime installer is run on a machine that does not have .NET Framework installed, the installation proceeds without any errors but there are a few steps which are not performed, which may cause errors later. The steps that are not performed are:

  • The machine.config entry for .NET Framework would not have entry for SQL Server Compact as a data provider.

  • The relevant assemblies of Compact runtime are not put in the Global Assembly Cache.

This can also happen if a user performs the following steps:

  • Install SQL Server Compact 4.0 runtime.

  • Install .NET Framework 3.5 SP1 or 4.

Note

This issue will also occur if there is already another version of .NET Framework and SQL Server Compact 4.0 installed on the machine.

For example, if a Clickonce application installs SQL Server Compact 4.0 and .NET Framework 4. When .NET Framework 2.0 and SQL Server Compact 4.0 are already installed on the machine, running the Clickonce application will not install SQL Server Compact 4.0 again. Therefore, the application will not make the machine.config entry for .NET Framework 4 for SQL Server Compact 4.0 as data provider.

The behavior of the application will be same if .NET Framework 4 and SQL Server Compact 4.0 are already installed on the machine and the Clickonce application installs .NET Framework 2.0 and SQL Server Compact 4.0.

To solve this problem, Reinstall / Repair SQL Server Compact runtime post .NET Framework installation.

3.7 The Encrypt property of the SqlCeConnection class is not used by the SQL Server Compact runtime

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

Note

The Encrypt property was deprecated in SQL Server Compact v3.5 release. This property was retained in SQL Server Compact 4.0 only for backward compatibility.

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

  • To create a new encrypted SQL Server Compact 4.0 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 change the encryption mode of an existing SQL Server Compact 4.0 database:

    C#:

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

    Visual Basic:

     
    Dim engine As SqlCeEngine = New SqlCeEngine("Data Source=Northwind.sdf;Password=<enterStrongPasswordHere>;") 
    engine.Compact("Data Source=Northwind.sdf;encryption mode= engine default;Password=<enterStrongPasswordHere>;")
  • To encrypt an unencrypted SQL Server Compact 4.0 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.8 The data types for parameters like SqlDbType and DbType should be explicitly set

SQL Server Compact 4.0 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 is 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.9 SQL Server Compact 4.0 does not support syncing of data with SQL Server

The SQL Server Compact 4.0 release does not support syncing of data with SQL Server using technologies like Microsoft Sync Framework, or merge replication, or remote data access (RDA).

3.10 Generation of the Entity Data Model will fail if there are duplicate constraint names in the SQL Server Compact schema

The generation of the Entity Data Model for SQL Server Compact will fail if there are duplicate constraint names in the SQL Server Compact schema.

In SQL Server Compact, the constraint names are unique within a table and this can allow duplicate constraint names in the database. This behavior is different from SQL Server, where the constraint names are unique across the database. If a SQL Server Compact schema has duplicate referential integrity (primary key – foreign key relationship) constraint names, the generation of the Entity Data Model using the ADO.NET Entity Framework’s Entity Data Model Wizard will fail. The workaround is to change the name of the duplicate constraint name to be unique across the database, for example, by adding the name of the table to the constraint name.

3.11 Creation of database will fail in SQL Server Compact 4.0 if there are multiple Entity Containers containing Entity Sets with same name

The Entity Sets in Entity Framework correspond to tables in SQL Server Compact and the table names are unique across the database. Entity Framework supports the entity sets with the same name if the Entity Container is different. SQL Server Compact does not support the concept of containers and treats the operation as only Create Table and throws the following error, “The specified table already exists”. Ensure that the names of the Entity Sets are unique, to ensure that the table can be created in the SQL Server Compact database.

3.12 Side-by-Side installation of English and Localized version of SQL Server Compact 4.0 Books Online causes an error

If a localized version of SQL Server Compact 4.0 Books Online is installed side-by-side with English Books Online, then if the English Books Online be uninstalled, the localized Books Online MSI is not launched and the following error is thrown:

Error: Microsoft Document Explorer cannot be shown because the specified help collection 'ms-help://MS.SSC.v40' is invalid.

The same issue happens when the English and the localized Books Online are installed side-by-side on the machine and the localized Books Online is uninstalled. The issue can be fixed by reinstalling the English or the localized Books Online.

[Return to Contents]

4.0 Additional Information