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)!

READ MORE - ADO.NET Sync Services for Devices sample

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.

READ MORE - SqlCeCmd tutorial part three – query options

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.

READ MORE - Merge replication performance tips

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.

READ MORE - Propose / Vote for new features on CodePlex

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("'}");

READ MORE - Scripting SQL datetime fields and avoiding localization problems

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.

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