Script SQL Server Compact tables in Management Studio just like SQL Server tables

One of the notable features missing from the SQL Server Management tools for SQL Compact is the ability to script DML (data manipulation language) that is: SELECT, UPDATE, INSERT, DELETE and DDL (data definition language): CREATE, DROP, ALTER when browsing SQL Server Compact tables in SQL Server Management Studio (SSMS) (Figure 1 below). Now, 1 year after the release of SQL Server 2008, you can now get the same features for SQL Compact tables (Figure 2 below) by installing the latest version of the free ExportSqlCE add-in for SSMS, available for download on CodePlex.

As always, let me know if there are any issues and/or feature requests by providing feedback here.

Figure 1: This is available in SSMS when browsing SQL Server tables.

Figure 2: With version 2 of the ExportSqlCE SSMS add-in, this is available when browsing SQL Server Compact tables.

Dealing with very large SQL Compact database files

On the SQL Server support blog, there is a new article, that documents two hot fixes relating to working with “very large” (in this case larger than 128 MB) SQL Compact database files.

SQL Compact Merge Replication “bible” updated

Rob Tiffany has just published the updated version of his SQL Compact Merge Replication book, http://blogs.msdn.com/robtiffany/archive/2009/07/29/enterprise-data-synchronization-with-microsoft-sql-server-2008-and-sql-server-compact-3-5-mobile-merge-replication.aspx. Strongly recommended for anyone who is involved in Merge Replication projects of any size.

SQL Server 2005 Compact Edition downloadable hotfix list

A number of SQL Compact 3.1 hot fixes have been released and are publicly available at the Microsoft support site. The table below gives an overview of the hotfixes. Note that the hotfixes are cumulative, meaning that the latest hotfix contains all previous hotfixes. Some hotfixes are only applicable for a particular platform, however. All hotfixes apply to SQL Compact 3.1, version 3.0.5300.0.

The number column indicates the revision number, so 7 would be version 3.0.5300.7 and so on.

No	KB	Title	Platforms
0	934386	FIX: You cannot use SQL Server 2005 Compact Edition on an ARMV4i-based device that is running Windows CE .NET 4.2	wce4
1	936978	FIX: An access violation occurs when you run the SqlCeEngine.Verify method to check for the consistency of a database in SQL Server 2005 Compact Edition or in SQL Server 2005 Mobile Edition	wce4
1	936487	FIX: 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	wce5, wce5-ppc
1	935769	FIX: 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"	wce4-ppc, wce5, wce5-ppc
2	938786	FIX: A .NET Compact Framework 2.0-based device application that calls the SqlCeEngine.Verify method against a SQL Server 2005 Compact Edition database stops responding	wce4, wce4-ppc
3	947002	FIX: 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"	wce4, wce4-ppc, wce5-ppc, wce5-phone
4	950440	FIX: The application seems to stop responding when an application executes a large query that has parameters in predicates in SQL Server 2005 Compact Edition	x86
5	951932	FIX: 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>]"	x86
7	953259	FIX: 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"	wce4-ppc, wce5-ppc
8	960142	FIX: 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 – see this for API changes	wce4, wce4-ppc, wce5, wce5-ppc, x86
9	967963	FIX: 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	wce4, wce4-ppc
11	968171	FIX: Error message when you try to create an encrypted database of SQL Server 2005 Compact Edition: "The operating system does not support encryption"	x86
13	970915	FIX: 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)"	wce4 (all), wce5 (all), x86
14	971970	FIX: 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	wce4 (all), wce5 (all), x86

 

Please let me know if there are errors or omissions.

SQL Compact 3.5 SP1 downloadable hotfix list

A number of SQL Compact 3.5 hot fixes have been released recently and are publicly available at the Microsoft support site. The table below gives an overview of the hotfixes. Note that the hotfixes are cumulative, meaning that the latest hotfix contains all previous hotfixes. Some hotfixes are only applicable for a particular platform, however. All hotfixes apply to SQL Compact 3.5 SP1, version 3.5.5692.0.

The number column indicates the revision number, so 7 would be version 3.5.5692.7 and so on.

No	KB	Title	Platforms
1	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"	x86, x64
2	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"	x86
3	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=<UserAppName>.exe" UPDATE: More info about required code changes and new api here	x86, wce4, wce4-ppc, wce5, wce5-ppc
5	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	wce5, wce5-ppc
7	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"	x86, wce5-ppc, wce5-phone
7	969858	FIX: 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	Same fix as above
9	970269	FIX: 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	x64
11	970414	FIX: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish	wce4-ppc, wce5-ppc
11	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	wce5, wce5-ppc
12	971027	FIX: 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"	x86, x64
12	972002	FIX: Error message when you try to create an encrypted database in SQL Server Compact 3.5: "The operating system does not support encryption"	x86
13	972390	FIX: 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½	x86
14	974068	FIX: 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"	x86, x64

 

Please let me know if there are errors or omissions.

ADO.NET Sync Services for Devices sample

A new sample has been made available at CodePlex, SyncComm. This sample/reusable framework demonstrates how to make WCF communication work the best possible way when using Compact Framework. It implements the following features: Correct WCF service configuration, use of zip compression for data transfer and batching of updates. All in order to improve performance and ease developer pain. Good stuff – while we are waiting for version 2 (and later)!

SqlCeCmd tutorial part three – query options

This is the third part of a 3 part series with some examples of sqlcecmd usage. The first part is here and the second here. This third part deals with querying database objects and formatting query output. (In other words, running SELECT statements)

To query a table

To  get query output, simply use the q- option, followed by the SELECT statement. This will give you a result listing the column names and table data. Note that long fields are truncated to 256 chars, use the xml option if you need all data in the field (see below).

To output to a file

You can use pipe characters (> or | ), or use the –o option to direct output to a file:

To avoid column names and dotted lines

Use the –h option. The number after the –h indicates the number of rows to print between the column headings. Using 0 (zero) causes the heading not to be printed.

To compress output

To make output more compact, use the –s option, which must be followed by the character you wish to use a separator, in this case semicolon. This is combined with the –w option, which removes any trailing spaces from the output to make it more compact.

To output all data as xml

Finally, the –x option will output the query results in XML DataSet format.

You can mix the various formatting options (-s, –w and –h) to get the result you desire.

This concludes the 3 part series on using SqlCeCmd to manage SQL Compact databases directly from the command line.