1.0 Introduction

    1.1 Obtaining SQL Server Compact 3.5 SP1

    1.2 SQL Server Compact 3.5 Books Online

    1.3 Microsoft Synchronization Services for ADO.NET

    1.4 ADO.NET Entity Framework

2.0 Supported Operating Systems and Platforms for SQL Server Compact 3.5

3.0 Known Issues

4.0 Additional Information

    4.1 Getting SQL Server Compact 3.5 Assistance

    4.2 Providing Feedback on SQL Server Compact 3.5

[Top]

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

1.2 SQL Server Compact 3.5 SP1 Books Online

1.3 Microsoft Synchronization Services for ADO.NET

1.4 ADO.NET Entity Framework

This release of SQL Server Compact 3.5 SP1 supports the following operating systems.

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 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 back to Visual Studio 2008 installation, first you must uninstall SQL Server Compact 3.5 SP1 Design Tools and SQL Server Compact 3.5 SP1 manually. Then, you must install SQL Server Compact 3.5 Design Tools and SQL Server Compact 3.5 from the Visual Studio 2008 installation media.
   You can find and install the Windows Installer files for SQL Server Compact 3.5 (SSCERuntime-<language>.msi) and for SQL Server Compact 3.5 Design Tools (SSCEVSTools-<language>.msi) from the WCU\SSCE folder on the Visual Studio 2008 installation media.
   
   
5. If you choose the repair option when installing SQL Server 2008, SQL Server Compact 3.5 SP1 Query Tools or SQL Server Compact 3.5 Service Pack (SP1) is not reinstalled automatically.
   You can find and install the Windows Installer files for SQL Server Compact 3.5 SP1 (SSCERuntime-<language>.msi) and SQL Server Compact 3.5 SP1 Query Tools (SSCESqlWbTools-<language>.msi) from the Servers\Setup folder on the SQL Server installation media.
   
   
6. An exception occurs when using the SQL Server 2008 Management Studio if the SQL Server Compact 3.5 SP1 is not installed.
   If the SQL Server Compact SP1 is not installed when using the SQL Server 2008 Management Studio, an exception occurs. To resolve this issue, install the Microsoft SQL Server Compact 3.5 SP1 from the SQL Server 2008 installation media or from the Microsoft Download Center.
   
   
7. The SQL Server Compact 3.5 SP1 (x86) Server Tools MSI does not recognize the Internet Information Services (IIS) installed on Vista (x64) or Windows 2008 Server operating systems.
   To resolve this issue, first install the IIS 6.0 Compatibility Components. For more information about installing the IIS 6.0 Compatibility Components, see IIS 6 Compatibility Components. Then, under the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\InetStp registry key, change the MajorVersion value to 6. Finally, install the SQL Server Compact 3.5 SP1 (x86) Server Tools and change the MajorVersion value back to 7.
   
   
8. After installing the SQL Server Compact 3.5 SP1 Device Runtime, any "Smart Device" project that references the System.Data.SqlServerCe.dll assembly and was created by the Visual Studio 2008 SP1 or Visual Studio 2008, cannot be opened back and forth. This is because the assembly versions of System.Data.SqlServerCe.dll in SQL Server Compact 3.5 SP1 (3.5.1.0) and SQL Server Compact 3.5 (3.5.0.0) are different. The following list describes possible scenarios and solutions:
   
   • Scenario 1:
     First, create a "Smart Device" project by using the Visual Studio 2008 and make sure that it references the System.Data.SqlServerCe.dll assembly. Then, install the Visual Studio 2008 SP1 and the SQL Server Compact 3.5 SP1 Device Runtime.
     Finally, open and build the project. When you build the project, the following error is displayed: "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." The reason is that the version of the System.Data.SqlServerCe.dll referenced in the project is different than the one present on the computer.
     To resolve this issue, first open the Solution Explorer from the View menu. Click the References and select the System.Data.SqlServerCe assembly. In the Properties window, set Specific Version property to False. Note that this solution does not require updating the assembly version of System.Data.SqlServerCe.dll while opening this project back and forth with Visual Studio 2008 and Visual Studio 2008 SP1 releases.
     
   • Scenario 2:
     First, install the Visual Studio 2008 and SQL Server Compact 3.5 Device Runtime. Then, open a "Smart Device" project that was previously created on a computer with Visual Studio 2008 SP1 and SQL Server Compact 3.5 SP1 Device Runtime. Finally, build the project. The build will succeed. However, the following error is displayed while debugging the application: "Can't find PInvoke DLL 'sqlceme35.dll'". This is because the project has a local copy of the System.Data.SqlServerCe.dll with version 3.5.1.0 but the dependent native files, including sqlceme35.dll, are not present on the computer.
     To resolve this issue, first open the Solution Explorer from the View menu. Click the References and select the System.Data.SqlServerCe assembly. In the Properties window, set Copy Local property to False. Then, remove the System.Data.SqlServerCe.dll assembly from the project's local folder. Click the References, select the System.Data.SqlServerCe assembly, and then right-click Remove. Click the References again, right-click and select Add Reference. In the Add Reference dialog box, click the .NET tab, select System.Data.SqlServerCe from the Component Name list, and then click OK.
     
   
   
9. Default constraints are replicated "as-is" when replicating a column of geometry type to SQL Server Compact subscriber. To resolve this issue, do one of the following:
   
   • Do not replicate default constraints for the article by disabling the schema option for it.
     
   • Remove the default constraint from the table before publishing.
     
   • Specify the default value as a literal instead of a geometric function.
     
   
   
10. 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 only for backward compatibility.

    
    Use the Encryption Mode property of the SqlCeConnection class to encrypt SQL Server Compact 3.5 SP1 database files. The following example shows 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()

11. 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.
    
    
12. 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 might try to guess the most appropriate data type for that parameter. This guess might 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 such cases, an exception might be thrown.
    To resolve this issue, we strongly recommend that you specify the data types of parameters for String and Binary data types that are not of fixed length. 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(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()

13. SQL Server Compact 3.5 SP1 database files (.sdf) can be opened by both the SQL Server Compact 3.5 and SQL Server Compact 3.5 SP1. However, in some cases, a database file format compatibility is restricted:
    
    • A case-sensitive database created by the SQL Server Compact 3.5 SP1 cannot be opened or recognized by the SQL Server Compact 3.5. Note that the case-sensitive collations are introduced starting with the SQL Server Compact 3.5 SP1 release.
      
    • The same database file cannot be opened by the SQL Server Compact 3.5 runtime and the SQL Server Compact 3.5 SP1 runtime simultaneously.
      
    
    
14. In this release, running queries that include a LEFT OUTER JOIN with a constant as a left operand do not return the correct results. This is because, SQL Server Compact does not evaluate such queries properly. 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 query = nwEntities.Orders.GroupBy(c => 10002, k => k.Order_ID); foreach (IGrouping<int, int> orderGroup in query) { // The result should include 1078 records. // However, in this release it returns only one record. Console.WriteLine("Key: {0}", orderGroup.Key); foreach (int order in orderGroup) { Console.WriteLine("Result: {0}", order); } } }

    
    
15. SQL Server Compact 3.5 SP1 Runtime Issues for the ADO.NET Entity Framework
    
    
    1. The correlated sub-queries, which are converted to scalar sub-queries internally, are not supported in this release. For such queries, the following error message is thrown: "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 ]".
       The reason is that the Entity Framework interprets the input query as a query with the CROSS APPLY or OUTER APPLY join types. If the right side of the join condition returns a scalar value, the join is converted into a scalar sub-query. The Entity Framework provider for SQL Server Compact needs to convert that scalar sub-query to an equivalent query with OUTER APPLY join type, which is supported by the SQL Server Compact. However, in this release, this conversion is not done properly. For example, the following query will throw an error 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()); } }

       
       
    2. An access violation exception occurs while executing an Entity Framework query if both the main query and the sub-query 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]

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

5.1 Getting SQL Server Compact Assistance

5.2 Providing Feedback on SQL Server Compact