Description of SQL Server Compact 3.5 Service Pack 1

Article translations Article translations
Article ID: 955965 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

This article describes Microsoft SQL Server Compact 3.5 Service Pack 1 (SP1). SQL Server Compact 3.5 SP1 updates the release version of SQL Server Compact 3.5. This article includes the following items:

MORE INFORMATION

Release notes

Version history

The following list contains the different releases of Microsoft SQL Server Compact:
  • Version 3.5 SP1 Microsoft SQL Server Compact 3.5 Service Pack 1 (SP1). This service pack was released together with Microsoft Visual Studio 2008 Service Pack 1 (SP1) and with Microsoft SQL Server 2008.
  • Version 3.5 Microsoft SQL Server Compact 3.5. This version was released together with Microsoft Visual Studio 2008.
  • Version 3.1 Microsoft SQL Server 2005 Compact Edition. This version was released at the same time for integration with Microsoft Visual Studio 2005 Service Pack 1 (SP1) and with Microsoft SQL Server 2005 Service Pack 2 (SP2).
  • Version 3.0 Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile). This version was released together with Microsoft Visual Studio 2005 and with Microsoft SQL Server 2005.
  • Version 2.0 Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE 2.0). This version was released together with Microsoft Visual Studio 2003.

New features

SQL Server Compact 3.5 SP1 includes the following new features:
  • Support for the ADO.NET Entity Framework. The Entity Framework enables you to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern yourself with the underlying database tables and columns in which this data is stored.

    Note Support for the ADO.NET Entity Framework lets you create flexible, strongly typed queries against the Entity Framework object context by using LINQ expressions and the LINQ standard query operators directly from the development environment.
  • Support for case-sensitive collations at the database level.
  • Administration of SQL Server Compact databases that are stored on a Windows Mobile device or on a desktop computer by using SQL Server Management Studio (SSMS) in SQL Server 2008.
  • Replication of the new data types in SQL Server 2008. These data types include date, time, datetime2, datetimeoffset, geography, and geometry. The new data types in SQL Server 2008 are mapped to nchar, nvarchar, image, and so on.
  • Native 64-bit support. Thirty-two-bit support has not changed.
  • Support for data replication with SQL Server 2008, with SQL Server 2005, and with SQL Server 2000 by using Microsoft Synchronization Services for ADO.NET 1.0 Service Pack 1 (SP1). Microsoft Synchronization Services for ADO.NET 1.0 SP1 is available for both desktop and mobile devices.
  • Support for data replication with SQL Server 2008 and with SQL Server 2005 by using merge replication and Remote Data Access (RDA).
  • Improved version compatibility between SQL Server Compact and SQL Server for merge replication.
  • SQL Server Compact 3.5 SP1 Server Tools
    • Can replicate data between SQL Server Compact 3.5 and SQL Server 2008 or SQL Server 2005.
    • Supports data replication between SQL Server 2005 Compact Edition or SQL Server 2005 Mobile Edition and SQL Server 2008 or SQL Server 2005.
    • Cannot be installed side by side with earlier versions of Server Tools on the computer that is acting as the server that is running Internet Information Services (IIS).
  • Supports Windows Server 2008.
In addition to the new features that are listed here, SQL Server Compact 3.5 SP1 includes the following new features that were included in SQL Server Compact 3.5:
  • Implements the time stamp (ROWVERSION) data type. ROWVERSION is a data type that exposes automatically generated binary numbers that are guaranteed to be unique in a database. Typically, this data type is used as a mechanism for version-stamping table rows.
  • Supports local transaction scope on desktop computers.
  • Adds improvements to Table Designer in Visual Studio 2008 to provide a user interface for creating primary-key and foreign-key relationships between tables.
  • Extends support for Transact-SQL statements as follows:
    • Nested query in FROM clause
    • CROSS APPLY and OUTER APPLY
    • CAST
    • TOP
    • SET IDENTITY INSERT
  • Supports Unicode characters of any locale. This includes Chinese GB 18030 characters.
  • Supports the development of desktop applications that use Microsoft Visual C# 2008 Express Edition and Microsoft Visual Basic 2008 Express Edition.
  • Supports side-by-side with SQL Server 2005 Compact Edition on desktop computers.
  • Supports backward-compatible encryption modes for older operating systems that have older encryption algorithms and adds newer, better encryption.
  • Supports LINQ to SQL. LINQ to SQL is a component of the LINQ project. LINQ to SQL provides a run-time infrastructure for managing relational data as objects without giving up the ability to query. It translates language-integrated queries into Transact-SQL for execution by SQL Server Compact and then translates the tabular results back into the objects as defined by the software developer. There is no designer support for SQL Server Compact in LINQ to SQL. The SqlMetal.exe utility must be used for SQL Server Compact. The SqlMetal command-line tool generates code and mapping for the LINQ to SQL component of the Microsoft .NET Framework. By default, the SqlMetal.exe file is located in the following folder:
    %ProgramFiles%\Microsoft SDKs\Windows\vn.nn\Bin
    The SqlMetal command-line tool can be used for the following:
    • From a database, to generate source code and mapping attributes or a mapping file
    • From a database, to generate an intermediate database markup language (.dbml) file for customization
    • From a .dbml file, to generate code and mapping attributes or a mapping file

Released languages

SQL Server Compact 3.5 SP1 for all packages is released in the following languages:
  • English
  • Brazilian Portuguese
  • Chinese Simplified
  • Chinese Traditional
  • French
  • German
  • Italian
  • Japanese
  • Korean
  • Russian
  • Spanish

Platforms supported

SQL Server Compact 3.5 SP1 can be installed on the following platforms:
Windows desktop computers
  • 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 Vista Home Basic SP1
  • Windows Vista Home Premium SP1
  • Windows Vista Business SP1
  • Windows Vista Enterprise SP1
  • Windows Vista Ultimate SP1
  • Windows Vista Starter 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 XP Professional SP3
  • Windows XP Home Edition SP3
  • Windows XP Media Center Edition 2005
  • Windows XP Media Center Edition 2004 SP3
  • Windows XP Tablet PC Edition SP3
  • Windows XP Embedded SP3
  • Windows XP Professional x64 SP3
  • Windows Embedded for Point of Service SP3
  • Windows Server 2003, Standard Edition SP2
  • Windows Server 2003, Enterprise Edition SP2
  • Windows Server 2003, Datacenter Edition SP2
  • Windows Server 2003 R2, Standard Edition
  • Windows Server 2003 R2, Enterprise Edition
  • Windows Server 2003 R2, Datacenter Edition
  • Windows Server 2003 x64, Standard Edition
  • Windows Server 2003 x64, Enterprise x64 Edition
  • Windows Server 2003 x64, Datacenter Edition
  • Windows Server 2003 R2 x64, Standard Edition
  • Windows Server 2003 R2 x64, Enterprise Edition
  • Windows Server 2003 R2 x64, Datacenter Edition
Windows Mobile devices
  • Windows CE 6.0
  • Windows CE 5.0
  • Windows CE 4.2
  • Windows Mobile 6.0
  • Windows Mobile 5.0
  • Windows Mobile 2003 for Pocket PC

Additional components

The SQL Server Compact 3.5 SP1 Windows Installer (.msi) files are described in this section. Some of these files are available for download from the Microsoft Download Center. To download the SQL Server Compact 3.5 SP1 components, visit the following Microsoft Web site:
http://www.microsoft.com/sql/editions/compact/downloads.mspx
The following list describes the additional SQL Server Compact 3.5 SP1 .msi files.

Note In this list, the placeholder language represents the product language of SQL Server Compact. For example, for English, the placeholder represents ENU, and for Japanese, the placeholder represents JPN.
  • SQL Server Compact 3.5 SP1 Design Tools (SSCEVSTools-language.msi)

    This file installs the SQL Server Compact design-time components together with Visual Studio 2008 SP1. The design-time components are the user interface, dialog boxes, and design-time environment. These are used to write applications for SQL Server Compact. The SQL Server Compact design-time components are installed in the following folder:
    %ProgramFiles%\Microsoft Visual Studio 9\Common7\IDE
    Sample files are installed in the following folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples
    These components are tied to Visual Studio and cannot be shipped independently of Visual Studio. SQL Server Compact samples and SQL Server Compact header files (for native development) are also installed by this file.
  • SQL Server Compact 3.5 SP1 (SSCERuntime-language.msi)

    This file installs the SQL Server Compact runtime components for desktop computers. It installs these components in the following central folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5
    These components are required for developing applications in Visual Studio and for running SQL Server Compact-based applications on the desktop. SSCERuntime-language.msi is also available for download from the Web. This file also installs the Microsoft Synchronization Services for ADO.NET 1.0 SP1 components. Synchronization Services components are installed in the following folder:
    %ProgramFiles%\Microsoft Synchronization Services\ADO.NET\v1.0
    For more information, see Microsoft Synchronization Services for ADO.NET Books Online. This resource is available on the Microsoft SQL Server Web site.
  • SQL Server Compact 3.5 SP1 for Devices (SSCEDeviceRuntime-language.msi)

    This file installs the SQL Server Compact devices' runtime components in the following folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Devices
    The runtime components are required for developing Windows Mobile and Windows CE device-based applications in Visual Studio and for deploying the applications on mobile devices. SSCEDeviceRuntime-language.msi is also available for download on the Microsoft SQL Server Web site.
  • SQL Server Compact 3.5 SP1 Server Tools (SSCEServerTools-language.msi)

    This file installs the SQL Server Compact Server Tools in the following folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\sync\SQL
    The components are required for connecting the SQL Server Compact database on a mobile device to a SQL Server 2008 or SQL Server 2005 database. SSCEServerTools-language.msi is also available for download on the Microsoft SQL Server Web site.
  • SQL Server Compact 3.5 SP1 Query Tools (SSCESqlWbTools-language.msi)

    This file installs the SQL Server Compact query-tool files in the following folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Tools
    Additionally, sample files are installed in the following folder:
    %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples
    SSCESqlWbTools-language.msi is part of the SQL Server Compact 3.5 SP1 release. The file is installed as part of SQL Server 2008.

    Note Starting with SQL Server Compact 3.5 SP1, the Query Tools component is also available when SQL Server 2008 is installed.

Known issues in this release

The known issues in SQL Server Compact 3.5 SP1 are as follows:
  • Integration of documentation (BOL) into Visual Studio and SQL Server Help

    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. They are not included with Visual Studio 2008 SP1 or with SQL Server 2008.

    To access these collections from the documentation for Visual Studio 2008 SP1 or for SQL Server 2008 and to access F1 Help from Visual Studio 2008 SP1 or from SQL Server 2008, add these collections to the Visual Studio and SQL Server Combined Help Collection. To do this, download SQL Server Compact 3.5 SP1 Books Online and Microsoft Synchronization Services for ADO.NET. Then, close all instances of the Visual Studio/SQL Server Combined Help Collection, of Visual Studio 2008, and of SQL Server 2008. When you reopen the Visual Studio and SQL Server 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.
  • Windows Mobile Support

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

    Upgrading to SQL Server Compact 3.5 or to SQL Server Compact 3.5 SP1 from SQL Server Compact 3.5 community technology preview (CTP) versions or from SQL Server Compact 3.5 beta versions is not supported. Upgrading to SQL Server Compact 3.5 SP1 from SQL Server Compact 3.5 or from SQL Server Compact 3.5 SP1 beta is supported.

    Upgrading from the CTP and beta versions of SQL Server Compact 3.5 to the release version is not supported. Uninstalling a 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 before you install the release version of Visual Studio 2008. To do this, take one of the following actions:
    • In Windows Vista, click Start, click Control Panel, and then double-click Programs and Features. Uninstall all CTP and beta installations of the following applications:
      • SQL Server Compact 3.5
      • SQL Server Compact 3.5 for Devices
      • SQL Server Compact 3.5 Design Tools
    • In Windows XP or in Windows Server 2003, click Start, click Control Panel, and then double-click Add or Remove Programs. Uninstall all CTP and beta installations of the following applications:
      • SQL Server Compact 3.5
      • SQL Server Compact 3.5 for Devices
      • SQL Server Compact 3.5 Design Tools
  • SQL Server Compact 3.5 SP1 is not uninstalled when Visual Studio 2008 SP1 is uninstalled

    Uninstalling Visual Studio 2008 SP1 does not uninstall SQL Server Compact 3.5 SP1 Design Tools and SQL Server Compact 3.5 SP1 automatically. If you want to revert to the Visual Studio 2008 installation, you must first manually uninstall SQL Server Compact 3.5 SP1 Design Tools and SQL Server Compact 3.5 SP1. Then, you must use the Visual Studio 2008 installation media to install SQL Server Compact 3.5 Design Tools and SQL Server Compact 3.5. The Windows Installer packages for SQL Server Compact 3.5 and for SQL Server Compact 3.5 Design Tools are located in the following folder on the Visual Studio 2008 installation media:
    WCU\SSCE
    The file name of the SQL Server Compact 3.5 package is SSCERuntime-Language.msi. The file name of the SQL Server Compact 3.5 Design Tools package is SSCEVSTools-Language.msi.

    Note The placeholder Language represents the product language of SQL Server Compact.
  • SQL Server Management Studio 2008 Integration

    If you select the Repair option when you install SQL Server 2008, SQL Server Compact 3.5 SP1 Query Tools and SQL Server Compact 3.5 SP1 are not reinstalled automatically. To resolve this issue, install the SQL Server Compact .msi files from the following folder on the SQL Server installation media:
    Servers\Setup
    The file name of the SQL Server Compact 3.5 SP1 package is SSCERuntime-Language.msi. The file name of the SQL Server Compact 3.5 SP1 Query Tools package is SSCESqlWbTools-Language.msi.

    Note The placeholder Language represents the product language.

    Important If SQL Server Compact 3.5 SP1 is not installed when you use SQL Server 2008 Management Studio, you receive the following error message:
    Unable to load Microsoft SQL Server Compact. Install Microsoft SQL Server Compact MSIs from the folder - Servers\Setup on the SQL Server installation media. For more details, refer to KB Article 952218.
    To resolve this issue, install the Microsoft SQL Server Compact 3.5 SP1 Query Tools and Microsoft SQL Server Compact 3.5 SP1 from the SQL Server 2008 installation media in the "Servers\Setup" folder or from the following Microsoft Download Center Web site:
    http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9
    For more information about this issue and how to resolve it, click the following article number to view the article in the Microsoft Knowledge Base:
    952218 Error message when you try to connect to SQL Server Compact 3.5 Service Pack 1 from SQL Server Management Studio in SQL Server 2008: "Unable to load Microsoft SQL Server Compact"
  • The SQL Server Compact 3.5 SP1 (x86) Server Tools .msi file does not recognize IIS when IIS is installed on Vista (x64) or on Windows 2008 Server

    For more information about this issue and how to resolve it, click the following article number to view the article in the Microsoft Knowledge Base:
    955966 SQL Server Compact 3.5 Service Pack 1 Server Tools are not installed in WOW mode when you run IIS 7.0 on a 64-bit Windows Server 2008-based computer or on a 64-bit Windows Vista-based computer
  • Visual Studio for Devices backward compatibility

    After you install SQL Server Compact 3.5 SP1 Device Runtime, any "Smart Device" project that references the System.Data.SqlServerCe.dll assembly and was created by using Visual Studio 2008 SP1 or Visual Studio 2008 cannot be opened back and forth. This issue occurs because the assembly versions of the System.Data.SqlServerCe.dll assembly in SQL Server Compact 3.5 SP1 (3.5.1.0) and in SQL Server Compact 3.5 (3.5.0.0) are different. Possible scenarios and their resolutions are as follows:
    • You create a "Smart Device" project by using Visual Studio 2008 and make sure that the project references the System.Data.SqlServerCe.dll assembly. Then, you install Visual Studio 2008 SP1 and SQL Server Compact 3.5 SP1 Device Runtime. When you open and then build the project, you receive the following error message:
      This operation requires a reference to SQL Server Compact 3.5. The project has a reference to a different version. Update the reference and try again.
      This issue occurs because the version of the System.Data.SqlServerCe.dll that is referenced in the project differs from the System.Data.SqlServerCe.dll file that exists on the computer. To resolve this issue, follow these steps:
      1. On the View menu, click Solution Explorer.
      2. Click References.
      3. Click to select the System.Data.SqlServerCe check box, and then set the Specific Version property to False in the Properties dialog box.
      Note This resolution does not require you to update the assembly version of the System.Data.SqlServerCe.dll file if you open the project back and forth by using Visual Studio 2008 and Visual Studio 2008 SP1.
    • You install Visual Studio 2008 and SQL Server Compact 3.5 Device Runtime. Then, you open a "Smart Device" project that was previously created on a computer that is running Visual Studio 2008 SP1 and SQL Server Compact 3.5 SP1 Device Runtime. When you build the project, the application builds successfully. However, when you try to debug the application, you receive the following error message:
      Can't find PInvoke DLL 'sqlceme35.dll'
      This issue occurs because the project has a local copy of the System.Data.SqlServerCe.dll with version 3.5.1.0. But the dependent native files are not present on the computer. These files include Sqlceme35.dll. To resolve this issue, follow these steps:
      1. On the View menu, click Solution Explorer.
      2. Click References.
      3. Click to select the System.Data.SqlServerCe check box, and then set the Copy Local property to False in the Properties dialog box.
      4. Remove the System.Data.SqlServerCe.dll assembly from the project's local folder.
      5. Click References.
      6. Right-click System.Data.SqlServerCe, and then click Remove.
      7. Click References.
      8. Right-click in the References area, and then click Add Reference.
      9. In the Add Reference dialog box, click the .NET tab.
      10. In the Component Name list, click to select the System.Data.SqlServerCe check box, and then click OK.
  • Default constraints are replicated "as-is"

    Default constraints are replicated "as-is" when an article that contains a column of type Geometry is replicated to a subscriber who is running SQL Server Compact. To resolve this issue, do one of the following:
    • Do not replicate default constraints. Instead, disable the schema option for the article before the article is replicated to the subscriber.
    • Remove the default constraint from the table before you publish.
    • Specify the default value as a literal instead of as a geometric function.
  • The "Encrypt" property of the "SqlCeConnection" class is not used
    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 The Encrypt property will be deprecated in a future release. The property is retained in SQL Server Compact 3.5 SP1 for backward compatibility only.

    Use the Encryption Mode property of the SqlCeConnection class to encrypt SQL Server Compact 3.5 SP1 database files. The following examples show how 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()
    
  • A ClickOnce application must be republished after you upgrade to Visual Studio 2008

    When you upgrade from Visual Studio 2005 to Visual Studio 2008, an encrypted SQL Server Compact database is not upgraded, and ClickOnce applications must be republished.

    Database (*.sdf) files that were created by earlier versions of SQL Server Compact are incompatible with SQL Server Compact 3.5 SP1. Only nonencrypted 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 republished after the project is upgraded in Visual Studio 2008.

    To manually upgrade the encrypted database files, follow these steps:
    1. Click Data.
    2. Click Add New Data Source.
    3. Click Add Connection.
    If you open an earlier version of the database file, you receive a message. If you click OK to the message, the Upgrade to SQL Server Compact 3.5 Database dialog box is displayed.

    Or, you can use the SqlCeEngine.Upgrade API for programmatic scenarios. For example, you can use code that resembles the following code samples.

    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 your project uses ClickOnce to publish applications, the application should be republished after the application is upgraded in Visual Studio 2008. When you republish the ClickOnce application in Visual Studio 2008, you may see the following warning message:
    SQL Server 2005 Compact Edition prerequisite could not be found for bootstrapping
    The warning message can be safely ignored.
  • SQL Server Compact 3.5 SP1 data types for parameters such as SqlDbType and DbType should be explicitly set

    If the data types for parameters such as SqlDbType and DbType are not explicitly set, a database engine may try to guess the most appropriate data type for that parameter. This guess may not be correct, especially for String and Binary data types that are not of fixed length (including large objects), such as nvarchar, ntext, varbinary, and image. In these cases, an exception error message may be thrown.

    To resolve this issue, we strongly recommend that you specify the data kinds of parameters for String and Binary data types that are not of fixed length. For example, you can use code that resembles the following code samples.

    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(nameCol nvarchar(128), blobCol ntext);";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "INSERT INTO BlobTable(nameCol, blobCol) VALUES (@nameParam, @blobParam);";
    SqlCeParameter paramName = cmd.Parameters.Add("nameParam", SqlDbType.NVarChar, 	128);
    SqlCeParameter paramBlob = cmd.Parameters.Add("blobParam", 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(nameCol nvarchar(128), blobCol ntext);"
    cmd.ExecuteNonQuery() 
    
    cmd.CommandText = "INSERT INTO BlobTable(nameCol, blobCol) VALUES (@nameParam, @blobParam);"
    Dim paramName As SqlCeParameter
    Dim paramBlob As SqlCeParameter
    paramName = cmd.Parameters.Add("nameParam", SqlDbType.NVarChar, 128)
    paramBlob = cmd.Parameters.Add("blobParam", SqlDbType.NText)
    paramName.Value = "Name1"
    paramBlob.Value = "Name1".PadLeft(4001)
    
    cmd.ExecuteNonQuery()
    
  • Database file format compatibility is restricted in some cases

    SQL Server Compact 3.5 SP1 database (.sdf) files can be opened in SQL Server Compact 3.5 and in SQL Server Compact 3.5 SP1. However, in some cases, database file format compatibility may be restricted. For example, database file format compatibility may be restricted in the following scenarios:
    • A case-sensitive database that is created in SQL Server Compact 3.5 SP1 cannot be opened or recognized in SQL Server Compact 3.5. Be aware that case-sensitive collations are introduced starting with SQL Server Compact 3.5 SP1.
    • The same database file cannot be opened at the same time by the SQL Server Compact 3.5 runtime and by the SQL Server Compact 3.5 SP1 runtime.
  • A query that contains a LEFT OUTER JOIN type with a constant as a left operand does not return the correct results

    When you run a query that includes a LEFT OUTER JOIN type with a constant as a left operand, correct results are not returned. This issue occurs because SQL Server Compact does not evaluate such queries correctly. The following is an example of such a query:
    SELECT * FROM table1 LEFT OUTER JOIN table2 ON 1 = table1.column1
    The following LINQ to Entities query also includes a constant value as the key selector function in the GroupBy method. This query is converted to a LEFT OUTER JOIN with a constant as a left operand internally and returns an incorrect value.

    C#
    using (NorthwindEntities nwEntities = new NorthwindEntities())
    {
       var customers = nwEntities.Customers
                       .GroupBy( c => 1, c => c.Customer_ID)
                       .Select( c => c);
    }
    
  • SQL Server Compact 3.5 SP1 runtime issues for the ADO.NET Entity Framework
    • Correlated subqueries that are converted to scalar subqueries internally are not supported in this release. For such subqueries, you receive the following error message:
      An error occurred while executing the command definition. See the inner exception for details." The Inner Exception contains the following message "There was an error parsing the query. [.., Token in error = AS ]
      This issue occurs because the Entity Framework interprets the input query as a query with the CROSS APPLY or OUTER APPLY join type. If the right side of the join condition returns a scalar value, the join is converted into a scalar subquery. The Entity Framework provider for SQL Server Compact has to convert that scalar subquery to an equivalent query with OUTER APPLY join type that is supported by SQL Server Compact. However, in this release, this conversion is not done correctly. For example, the following query will throw an error message in this release:

      C#
      using (NorthwindEntities nwEntities = new NorthwindEntities())
      {
          var orders = nwEntities.Employees
                      .Select(employee => employee.Orders.Max(order => order.Order_ID));
          foreach (var order in orders)
          {
             Console.WriteLine(order.ToString());
          }
      }
      
      • An access violation exception occurs while executing an Entity Framework query if both the main query and the subquery include a DISTINCT keyword and a join condition is indicated with an OUTER JOIN clause. For example, the following query will create an access violation:
        SELECT DISTINCT [o].[CustomerID]
        FROM (SELECT DISTINCT * FROM [Orders]) AS [o]
        LEFT OUTER JOIN [Customers] AS [c] ON [o].[CustomerID] = [c].[CustomerID]
        

List of issues that are fixed

This service pack fixes the following issues:
  • 923979 Error message when you try to insert a row or perform other database operations in a SQL Server 2005 Mobile Edition database: "Wrong page possible database corruption"
  • 933463 Managed applications that use SQL Server 2005 Mobile Edition may stop unexpectedly if the location of the SQL Mobile DLLs changes
  • 934386 You cannot use SQL Server 2005 Compact Edition on an ARMV4i-based device that is running Windows CE .NET 4.2
  • 935769 Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size"
  • 936487 Managed applications that use SQL Server 2005 Compact Edition 3.1 may stop unexpectedly on Windows Mobile 5.0 ARMV4i-compatible devices if the location of the SQL Server Compact Edition DLLs changes
  • 938786 .NET Compact Framework 2.0-based device application that calls the SqlCeEngine.Verify method against a SQL Server 2005 Compact Edition database stops responding
  • 947002 Error message when you try to delete the rows from the table in SQL Server 2005 Compact Edition or in SQL Server Compact 3.5: "Major Error 0x80004005, Minor Error 0. Attempted to divide by zero"
  • 950440 The application seems to stop responding when an application executes a large query that has parameters in predicates in SQL Server 2005 Compact Edition
  • 951932 Error message when you create a subscription in SQL Server 2005 Compact Edition to a merge publication on SQL Server 2000: "The table name is not valid. [Token line number (if known) = 1,Token line offset (if known) = 22,Table name = <TableName>]"
  • 953259 Error message when you run an SQL statement that uses the Charindex function in a database that uses the Czech locale in SQL Server 2005 Compact Edition: "The function is not recognized by SQL Server Compact Edition"
This service pack fixes the following issues that were not previously documented in a Microsoft Knowledge Base article:

SQL Server Compact 3.5 SP1
  • During the merge replication of data from SQL Server 2005 to SQL Server Compact 3.5, incorrect Transact-SQL statements may be created. Additionally, data changes are not applied to SQL Server Compact. For example, this problem may occur when an incorrect Transact-SQL statement such as the following is created:
    not null default n e w i d ( )
    You may expect a Transact-SQL statement such as the following to be created:
    not null default newid()
  • You experience slower query performance in SQL Server Compact 3.5 compared to SQL Server 2005 Compact Edition. This problem is caused by different query plans. If you run the following code, an incorrect query plan is generated in SQL Server Compact 3.5, and table scan is used instead of index scan:
    cmd.CommandText = @"select * from  Customers where Customers.Customer_ID = @v0";
    cmd.Parameters.Add("@v0", SqlDbType.NVarChar);
    rdr = cmd.ExecuteReader();
  • A TransactionScope parameter is committed regardless of whether ts.Complete is called. For example, this problem occurs when LINQ to SQL DataContext is used, as in the following example code with SQL Server Compact 3.5:
    using System;
    using System.Data.Linq;
    using System.Transactions;
    
    namespace bug
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataContext ctx = new DataContext("Test.sdf");
                using (TransactionScope ts = new TransactionScope())
                {
                    ctx.ExecuteCommand("INSERT INTO x (y) VALUES (2)");
                }
            }
        }
  • When you use SQL Server Compact together with ADO.Net Entity Framework, values that are larger than 8,000 bytes cannot be inserted into a SQL Server Compact table that contains binary large object columns.
  • If an index is stale, update statistics fail when the connection to SQL Server Compact 3.5 is opened in a read-only mode.
  • You receive an Access Violation error message in the GetColumnRowSet process for scrollable result sets. Additionally, the OpenSharedMemory process does not reset the pError object when shared memory creation fails in a Global namespace.
  • Disposing of a Command object after a Connection process may throw an exception error message. This problem may occur when the following code is run:
    SqlCeConnection cn;
    SqlCeCommand cmd;
    ...
     
    cn.Dispose();
    cmd.Dispose(); 
    
  • An Access Violation error message is thrown when a Select statement does not return all columns that are sorted by the Group By parameter and by the Order By parameter. For example, this problem may occur with the following Select statement:
    SELECT        [Company Name], [Contact Name]
    FROM            Customers
    GROUP BY [Customer ID], [Company Name], [Contact Name]
    ORDER BY [Customer ID], [Company Name], [Contact Name]
    

  • CASCADE DELETE does not work correctly if there is only one element in the table and an AND condition is used in a WHERE clause. Using "NOT" with "OR" works correctly. For example, the following query does not work correctly:
    Select from table where (a=x AND b=y)
  • A null value in the Select list may cause a Native exception error message in the ExecuteReader process intermittently. The exception is not caught by the SqlCeException class, and you receive the following native exception error message:
    A native exception has occurred in SqlMobileCrash.exe. Select quit And then restart this program or select details for more information.

    Exception Code: 0xc0000005
    Exception Address: 0x00d1aa68
    Reading: 0x0027f0000
  • The Table Name parameter is treated as case-sensitive when the SET IDENTITY_INSERT command is used. This problem occurs because metadata should always be case-insensitive.
  • Inserts to columns of the type Float fail if the IDbCommand object is used. For example, this problem occurs with the following code:
    try
                {
                    string ConnectionString = "Data Source='Northwind.sdf'";
                    IDbConnection conn = new SqlCeConnection(ConnectionString);
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "insert into \"newtable\"(\"t1\")values(@p0)";
                    conn.Open();
                    IDbDataParameter p;
                    p = cmd.CreateParameter();
                    p.Value = 7.6M;
                    p.ParameterName = "@p0";
                    cmd.Parameters.Add(p);
                    IDbTransaction t = cmd.Connection.BeginTransaction();
                    cmd.Transaction = t;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    p.Value = 10M;
                    cmd.ExecuteNonQuery(); // Exception is thrown here
                    t.Commit();
                }
                catch (Exception ex)
                { 
                }
    
  • The SqlCeResultSet.GetEnumerator command throws a StackOverflowException error message and the SqlCeDataReader.GetEnumerator command throws a NotSupportedException error message when the following code is run:
    SqlCeConnection conn = new SqlCeConnection(@"Data Source=Northwind.sdf");
    conn.Open();
    
    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "Select * from categories";
    SqlCeResultSet res = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable | 	ResultSetOptions.Sensitive);
    IEnumerator e= res.GetEnumerator(); // Exception is thrown here.
    conn.Close();
    
  • When a RAPI call simulates a System.Web (ASP.NET) scenario, SQL Server Compact may throw a null reference exception error message.
SQL Server Compact 3.5
  • Merge initializations cannot run an Alter Table statement for the primary key index constraint if a maintenance plan to rebuild the index is run. Merge initialization fails, and you receive the following error message:
    The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = Categories ] HRESULT 0x80040E14 (26100).

    The SQL statement failed to execute. [ SQL statement = ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [CategoryID] )WITH PAD_INDEX , FILLFACTOR = 85 ] HRESULT 0x80040E14 (28560)
  • If a Windows Mobile 2003-based Pocket PC runs multiple threads and processes to access the same database, the application may exit with a random error message.
  • In Microsoft Visual Studio 2008, a database connection cannot be established to a SQL Server Compact database. This problem occurs if the database is larger than 256 megabytes (MB).
  • The following SQL Server Compact native binaries are now signed by Microsoft.

    Global .dll files
    • Sqlcese35.dll
    • Sqlcecompact35.dll
    • Sqlceqp35.dll
    • Sqlceoledb35.dll
    • Sqlceme35.dll
    • Sqlceca35.dll
    • Sqlcesa35.dll
    • Sqlcerp35.dll
    Language-specific .dll files
    • Sqlceer35br.dll
    • Sqlceer35cn.dll
    • Sqlceer35cs.dll
    • Sqlceer35da.dll
    • Sqlceer35de.dll
    • Sqlceer35el.dll
    • Sqlceer35en.dll
    • Sqlceer35es.dll
    • Sqlceer35fi.dll
    • Sqlceer35fr.dll
    • Sqlceer35it.dll
    • Sqlceer35ja.dll
    • Sqlceer35ko.dll
    • Sqlceer35nl.dll
    • Sqlceer35no.dll
    • Sqlceer35pl.dll
    • Sqlceer35ps.dll
    • Sqlceer35pt.dll
    • Sqlceer35ru.dll
    • Sqlceer35sv.dll
    • Sqlceer35tw.dll
  • An access violation exception error message is thrown when a query has multiple OR predicates, as in the following code example:
    create table t1 (c1 int);
    create index t1_idx on t1(c1);
    insert into t1 values (1);
    insert into t1 values (2);
    insert into t1 values (3);
    
    create table t2 (col1 int, col2 int);
    insert into t2 values (2,3);
    insert into t2 values (1,3);
    insert into t2 values (3,3);
    
    select * from t1,t2 where c1 = col1 or c1 = col2 or c1 = 1;
    
  • You run a Windows XP-based computer that uses a FAT32 file system. If a SQL Server Compact database is accessed at the same time by a Windows service and by an application, the application crashes.
  • The SqlCeEngine Compact method sets the Autoinc_Next value incorrectly when the Maximum Allocated value in an assigned range is deleted.

    If the Maximum Allocated value is deleted, the Autoinc_Next value will be set to a value 1 greater than the current maximum value that is used by a given article within its range.
  • Unexpected results may occur when records that contain special characters are accessed in a TableDirectIndexSearch statement and in a Select statement.
  • Microsoft Visual Studio 2005 Query Designer adds square brackets for date functions.
  • During a merge replication of data between SQL Server and SQL Server Compact, you receive the following error message:
    The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.
  • A FormatException error message is thrown when a string is concatenated with the parameter of type String. For example, this problem occurs when you run the following code:
    SqlCeConnection con = new SqlCeConnection("Test.sdf"); 
    SqlCeCommand cmd = con.CreateCommand();
    cmd.CommandText = "select firstname + ' ' + lastname from person";
    bool i = cmd.ExecuteReader().Read();
    cmd.CommandText = "select firstname + @p0 + lastname from person";
    cmd.Parameters.Add("@p0", " ");
    i = cmd.ExecuteReader().Read();
  • Duplicated parameter names are not enabled.
  • If a "Delete", "Read", "ReadPrevious" sequence occurs when you use a Select statement that contains an Order By clause, an exception error message occurs.
  • SELECT DISTINCT * statements are not allowed when an ORDER BY clause is used.
  • When a constraint name is specified, a column cannot be added to an existing populated table that contains NOT NULL values and DEFAULT values. For example, this problem occurs when you run the following code:
    ALTER TABLE Categories ADD COLUMN bitcol1 bit NOT NULL CONSTRAINT MyConstraint DEFAULT (1);
  • When you run the following query, an incorrect index is selected:
    SELECT store_uid, stock_price, stock_class, stock_number, ticket_number, 
    pocket, max(last_reorder_date) last_reorder_date, reorder_point, 
    last_order_qty, sys_line_flag, stock_status, max(last_ship_date) 
    last_ship_date, max(last_scan_date) last_scan_date, 
    max(back_order_status) back_order_status, max(unit_last_scan_date) 
    unit_last_scan_date, max(revision_target) revision_target 
    
    FROM 
    tbl_master_chart 
    
    WHERE store_uid = 22359 and stock_price = '0399' and 
    stock_class = 'EGB' and stock_number = '4759' 
    
    GROUP BY store_uid, 
    stock_price, stock_class, stock_number, ticket_number, pocket, 
    reorder_point, last_order_qty, sys_line_flag, stock_status 
    
    ORDER BY 
    sys_line_flag DESC, ticket_number
    
  • If you use the ReadAbsolute (negative integer) procedure to retrieve the getString value of a row, you receive the following exception error message:
    No Data Exists for the row/column

Properties

Article ID: 955965 - Last Review: August 6, 2008 - Revision: 1.0
APPLIES TO
  • Microsoft SQL Server Compact 3.5
Keywords: 
kbtshoot kbexpertiseinter kbinfo KB955965

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com