4/23/2008 10:26:41 AM

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 1 (SP1) is the service pack release for SQL Server Compact 3.5. SQL Server Compact 3.5 SP1 is a small footprint, in-process database engine that allows developers to build robust applications for Windows desktops and mobile devices.

1.1 Obtaining SQL Server Compact 3.5 SP1

SQL Server Compact 3.5 SP1 installs with SQL Server 2008 and Visual Studio 2008 SP1, and is also available from the Web downloads listed below:

1.2 SQL Server Compact 3.5 SP1 Books Online

SQL Server Compact 3.5 SP1 Books Online provides detailed information about the development, administration, and deployment of SQL Server Compact 3.5 SP1 on Windows desktop computers, and on Windows Mobile devices. SQL Server Compact 3.5 SP1 Books Online does not install with Visual Studio 2008 SP1 Books Online or SQL Server 2008 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 Visual Studio 2008 SP1 or SQL Server 2008 documentation and to access F1 Help from Visual Studio 2008 SP1 or SQL Server 2008, you must add the collection to the Visual Studio Combined Help Collection. For instructions on adding the collection, see the Known Issues section.

1.3 Microsoft Synchronization Services for ADO.NET

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

This release of SQL Server Compact 3.5 SP1 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 SP1
  • Windows Vista Home Premium SP1
  • Windows Vista Business SP1
  • Windows Vista Enterprise SP1
  • Windows Vista Ultimate SP1
  • Windows Vista Starter Edition SP1
  • Windows Vista Home Basic x64 SP1
  • Windows Vista Home Premium x64 SP1
  • Windows Vista Business x64 SP1
  • Windows Vista Enterprise x64 SP1
  • Windows Vista Ultimate x64 SP1
  • Windows Server 2008 Standard Server
  • Windows Server 2008 Standard Server (without Hyper-V)
  • Windows Server 2008 Enterprise
  • Windows Server 2008 Enterprise (without Hyper-V)
  • Windows Server 2008 Data Center
  • Windows Server 2008 Data Center (without Hyper-V)
  • Windows Server 2008 Web Edition
  • Windows Server 2008 Standard Server x64
  • Windows Server 2008 Standard Server x64 (without Hyper-V)
  • Windows Server 2008 Enterprise x64
  • Windows Server 2008 Enterprise x64 (without Hyper-V)
  • Windows Server 2008 Data Center x64
  • Windows Server 2008 Data Center x64 (without Hyper-V)
  • Windows Server 2008 Web Edition x64

Windows Mobile Devices

3.0 Known Issues

  1. SQL Server Compact 3.5 SP1 Books Online (BOL) and Microsoft Synchronization Services for ADO.NET are available as a Web download only from the Microsoft Download Center and do not ship with Visual Studio 2008 SP1 or SQL Server 2008. To access these collections from the Visual Studio 2008 SP1 or SQL Server 2008 documentation and to access F1 Help from Visual Studio 2008 SP1 or SQL Server 2008, you must add these collections to the Visual Studio and SQL Server Combined Help Collection. To do that, download SQL Server Compact 3.5 SP1 Books Online and Microsoft Synchronization Services for ADO.NET. After downloading and installing the SQL Server Compact 3.5 SP1 Books Online and Microsoft Synchronization Services for ADO.NET Books Online, close all instances of the Visual Studio/SQL Server Combined Help Collection and Visual Studio 2008/SQL Server 2008. When you re-open the Combined Help Collection, SQL Server Compact 3.5 SP1 Books 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.

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

  3. Upgrading to SQL Server Compact 3.5 or SQL Server Compact 3.5 SP1 from SQL Server Compact 3.5 CTP or SQL Server Compact 3.5 beta versions is not supported. Upgrading from SQL Server Compact 3.5 or SQL Server Compact 3.5 SP1 beta to SQL Server Compact 3.5 SP1 is supported.

    Upgrading from the community technology preview (CTP) and beta versions of SQL Server Compact 3.5 to the release version is not supported. Uninstalling the CTP or beta version of Visual Studio 2008 does not uninstall SQL Server Compact 3.5. You must remove the CTP or beta installations of SQL Server Compact 3.5 manually before you install the release version of Visual Studio 2008, as follows:
    1. Uninstall the CTP or beta version of Visual Studio 2008.
    2. On Windows Vista, click Start, then Control Panel, and then double-click Programs and Features. Uninstall all CTP and beta installations of SQL Server Compact 3.5, SQL Server Compact 3.5 for Devices and SQL Server Compact 3.5 Design Tools.
    3. On Windows XP or Windows Server 2003, click Start, then Control Panel, and then double-click Add or Remove Programs. Uninstall all CTP and beta installations of SQL Server Compact 3.5, SQL Server Compact 3.5 for Devices, and SQL Server Compact 3.5 Design Tools.

  4. SQL Server Compact 3.5 SP1 does not use the Encrypt property of the SqlCeConnection class.

    The Encrypt property of the SqlCeConnection class should not be used.
    Note:
    Encrypt property will be deprecated in a future release. The property is retained in SQL Server Compact 3.5 SP1 only for backward compatibility.

    Use the Encryption Mode property of the SqlCeConnection class to encrypt SQL Server Compact 3.5 SP1 database files. Uses of encryption mode with examples are given below:

    1. To create a new encrypted SQL Server Compact 3.5 SP1 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()
    2. 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 SP1 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>;")
    3. To change the encryption mode of an existing SQL Server Compact 3.5 SP1 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>;")
    4. To encrypt an unencrypted SQL Server Compact 3.5 SP1 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>;")
      
  5. While upgrading Visual Studio 2005 to Visual Studio 2008, encrypted SQL Server Compact database is 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 SP1. Only unencrypted database files from earlier versions of SQL Server Compact are upgraded when a Visual Studio 2005 project is upgraded to Visual Studio 2008. Encrypted database files have to be manually upgraded. If the Visual Studio 2005 project uses ClickOnce to publish applications, the application should be re-published after the project has been upgraded in Visual Studio 2008.

    The 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. Code sample is shown below:
      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>;")
    If the project uses ClickOnce to publish applications, the application should be re-published after it has been upgraded in Visual Studio 2008. When you re-publish the ClickOnce application in Visual Studio 2008, you might see a warning that SQL Server 2005 Compact Edition prerequisite could not be found for bootstrapping. The warning should be ignored.

  6. SQL Server Compact 3.5 SP1 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 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()
    

4.0 Additional Information