Neither dead nor resting: SQL Mobile Merge Replication joys, DataSet woes.

Replicating dataApologies for the radio silence recently – it’s been very hectic! 

I’ve been exploring a couple of things. 

SQL Mobile Merge Replication is amazing stuff.  Using a “Publication” and “Subscription” metaphor, it allows you to synchronize parts of a SQL 2005 database with your mobile device. 

It really “just worked” for me.  All the heavy lifting required to sync parts of the database with a SQL Mobile database on my PocketPC was done automatically.

I’m having more grief with DataSets on the Compact Framework (for PocketPC 2003).  My Primary Key in all my data tables is of type Guid, which is necessary for Merge Replication.  But when I try to add a DataSet based on one of those tables, I get Visual Studio saying “Some updating commands could not be generated automatically.  The database returned the following error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.”

I manually created my own SelectCommand, UpdateCommand and DeleteCommand for each TableAdapter, but I still can’t seem to get DataGrids to work properly.  In the Designer, the DataGrid Tasks quick action glyph won’t let me Preview Data or Generate Data Forms, insisting “Error invoking ‘Preview Data…’. Details: Length cannot be less than zero. Parameter name: length”

Has anyone got this to work successfully?  If someone would look at my .sdf (SQL Mobile DB) and see if I’m missing something glaringly obvious, I’d get you something really nice.  [Update: no… really, I would… and you’d have my eternal gratitude…]

My head a splode.

Comments (2)

  1. Matt Patterson says:

    I just came across this problem myself, and here are my findings so far:

    To get around the Visual Studio code generation error, you will need your PK field to be a different field than your row GUID field that is needed for replication.  SQL Mobile treats the row GUID field as being read-only and disallows it from being used in the update or delete commands.  I’m currently using two GUIDs, one for replication and one for joins and business logic.

  2. RobBurke says:

    Thanks so much for that!  How painful, though, to have to use 2 GUIDs!  I will give this a try shortly and try to replicate your replication..