Microsoft's Embedded Database - SQL Server Compact - Team Blog Tweet

SQL Server Compact is a SKU of SQL Server family of products from Microsoft. This is a thin light-weight, embedded, in-proc database engine which supports synchronization to SQL Server, etc.

Microsoft SQL Server Compact 3.5 with Service Pack 2 released with Visual Studio 2010 and on the Web.

The latest release of SQL Server Compact also referred to as SQL CE or SQL Server Mobile, released with Visual Studio 2010 on the 12th of April 2010. SQL Server Compact 3.5 SP2 is a SQL Server Compact 3.5 with SP2 release, meaning that installing it on a machine using the Windows Installer (MSI) file upgrades the existing installation of SQL Server Compact 3.5 or SQL Server Compact 3.5 SP1 on the machine to SP2.

The main features for SQL Server Compact 3.5 SP2 are as given below:

* Supports working with a SQL Server Compact 3.5 database using the Transact-SQL Editor in Visual Studio 2010. The Transact-SQL Editor can be used to run free-text Transact-SQL queries against a SQL Server Compact 3.5 database. The Transact-SQL Editor also provides the ability to view and save detailed estimated and actual query show-plans for SQL Server Compact 3.5 databases. Previously, the functionality provided by the Transact-SQL Editor was only available through SQL Server Management Studio.
* New classes and members named SqlCeChangeTracking have been added to the System.Data.SqlServerCe namespace to expose the internal change tracking feature used by Sync Framework to track changes in the database. The SQL Server Compact change tracking infrastructure maintains information about inserts, deletes, and updates performed on a table that has been enabled for change tracking. This information is stored both in columns added to the tracked table and in system tables maintained by the tracking infrastructure. By using System.Data.SqlServerCe.SqlCeChangeTracking one can configure, enable, and disable change tracking on a table, and also access the tracking data maintained for a table. The API can be used to provide functionality in a number of scenarios. For example it can be used to provide custom implementations of client-to-server or client-to-client sync for occasionally connected systems (OCS) or to implement a custom listener application.
* The managed assemblies of SQL Server Compact for use by the applications that privately deploy SQL Server Compact are installed in the folder %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Private. Using these assemblies ensure that the application uses the privately deployed version of Compact even when a lower version of SQL Server Compact 3.5 is installed in the GAC.
* Visual Studio 2010 installs both the 32-bit and 64-bit versions of SQL Server Compact 3.5 SP2 on a 64-bit machine. If a SQL Server Compact application is deployed using Click Once in Visual Studio 2010 then both the 32-bit and the 64-bit version of SQL Server Compact are installed on a 64-bit machine
* SQL Server Compact 3.5 SP2 supports Windows 7 and Windows Server 2008 R2, and can sync data using merge replication with SQL Server 2008 R2 November CTP.
* A list of the bug fixes in SQL Server Compact 3.5 SP2 will be also released as a KB article similar to the KB article - http://support.microsoft.com/default.aspx?scid=KB;%5bLN%5d;955965 within two weeks. Apart from the bug fixes, the hot fixes that were released for SQL Server 2005 Compact Edition or SQL Server Compact 3.5 SP1 have been rolled up in SQL Server Compact 3.5 SP2. The list of hot fixes is as given below:
o http://support.microsoft.com/kb/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"
o http://support.microsoft.com/kb/958478: Error message when you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses"
o http://support.microsoft.com/kb/959697: Error message when you try to open a database file from a CD in SQL Server Compact 3.5 with Service Pack 1: "Internal Error using read only database file"
o http://support.microsoft.com/kb/960142: An error message is logged, and the synchronization may take a long time to finish when you use an application to synchronize a merge replication that contains a SQL Server 2005 Compact Edition subscriber
o http://support.microsoft.com/kb/963060: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: "UpdateStatistics Start app=.exe"
o http://support.microsoft.com/kb/967963: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5
o http://support.microsoft.com/kb/968171: Error message when you try to create an encrypted database in SQL Server 2005 Compact Edition: "The operating system does not support encryption"
o http://support.microsoft.com/kb/968864: Error message when you run a query in SQL Server Compact 3.5: "The column name cannot be resolved to a table. Specify the table to which the column belongs"
o http://support.microsoft.com/kb/969858: Non-convergence occurs when you synchronize a SQL Server Compact 3.5 client database with the server by using Sync Services for ADO.NET in a Hub-And-Spoke configuration
o http://support.microsoft.com/kb/970269: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1
o http://support.microsoft.com/kb/970414: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish
o http://support.microsoft.com/kb/970915: Error message when you synchronize a merge replication with SQL Server 2005 Compact Edition subscribers: "A column ID occurred more than once in the specification. HRESULT 0x80040E3E (0)"
o http://support.microsoft.com/kb/971027: Error message when you upgrade a very large database to SQL Server Compact 3.5: "The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only"
o http://support.microsoft.com/kb/971273: You do not receive error messages when you run a query in a managed application that returns columns of invalid values in SQL Server Compact 3.5
o http://support.microsoft.com/kb/971970: You cannot insert rows or upload changes into the SQL Server 2005 Compact Edition subscriber tables after you run the "sp_changemergearticle" stored procedure or you add a new merge publication article when another article has an IDENTITY column
o http://support.microsoft.com/kb/972002: Error message when you try to create an encrypted database in SQL Server Compact 3.5: "The operating system does not support encryption"
o http://support.microsoft.com/kb/972390: The application enters into an infinite loop when you run an application that uses Microsoft Synchronization Services for ADO.NET to synchronize a SQL Server Compact 3.5 database
o http://support.microsoft.com/kb/972776: When the application calls the SqlCeConnection.Close method or the SqlCeConnection.Dispose method in SQL Server Compact 3.5, the application may stop responding at the method call
o http://support.microsoft.com/kb/974068: Error message when an application inserts a value into a foreign key column in SQL Server Compact 3.5: "No key matching the described characteristics could be found within the current range"

Labels: