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.

Merge replication performance tips

The SQL Server Support team has posted some advice for improving initial subscription performance with SQL Compact 3.5, which for various reasons has decreased significantly compared to SQL CE 2.0 with SQL Server 2000. Note, that when pre-creating on the desktop you must use Windows XP/Server 2003 and not Vista/Server 2008, or index rebuild will occur on the device.

Propose / Vote for new features on CodePlex

If you have any ideas and suggestions for the future versions of the ExportSqlCe command line utility or SSMS 2008 addin, please go here and vote for or propose new features. Any ideas and suggestions are welcome, to the benefit for all users of the tools.

Scripting SQL datetime fields and avoiding localization problems

If you need to export you data to a .sql file for various reasons, you have different options when “serializing” datetime fields. Often they are scripted as: ‘2009-05-11 11:00’ or ‘May 5 2009 12:00AM’. This will break if the script is run with a tool/server that uses a different locale (like a European/Asian), and cause either run-time errors or even worse, incorrect dates in the database.

The Database Publishing Wizard / Publish to Provider… uses this format:

CAST(0x00008C3300000000 AS DateTime)

which is probably correct and portable, but not very notepad friendly.

I use the ODBC Escape sequence formatting, which looks like this, and works across any locale (and is more friendly on the eye!):

{ts ‘2009-05-11 23:00:00’}

Here’s the .NET code to create this format:

_sbScript.Append("{ts '");
_sbScript.Append(date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
_sbScript.Append("'}");

Script SQL Compact data and schema directly from SQL Server 2008 Management Studio (Express)

I have just released ExportSqlCE support for scripting SQL Compact data and schema directly from the SQL Server Management Studio Object Explorer. Read more about it here.

SQL Compact version on Windows Mobile 6.5

I have just installed the Windows Mobile 6.5 emulator, and just wanted to check the version of SQL Compact installed on this OS – the version is: 3.0.5300.8, which corresponds to this hotfix: http://support.microsoft.com/kb/960142 (and all hotfixes prior to this build).

New features in Sync Framework

A 90 minute webcast detailing the new features in the next version of the Microsoft Sync Framework (version 2) is now available for offline viewing and PPT download. The new version with the features presented here will RTM in 2nd half of 2009 (and a CTP will come very soon – June (?)).

Notable new features (relating to SQL Compact) include:

P2P Sync with SQL Compact 23:00– both on desktop and devices – provides ability to Sync 2 SQL Compact databases with each other! Can’t wait to try that out!

Snapshot Initialization 39:40 pre generate the SQL Compact file on the server and then download to the client, and only incremental changes need to be downloaded afterwards. Limits time to get initial data down, and decrease server load. Initially all data is downloaded to the web service in a “source” SQL Compact file, and based on this a special snapshot SQL Compact file is created. The client know that this is a snapshot, and will only download incremental changes after getting the snapshot.

Opt out of ADO.NET DataSets – mapping to a developer defined payload. (Not in version 2)

Using SqlCeResultSet for client side inserts – to improve performance.

UPDATE: CTP2 is now available for download.