This update solves an interesting issue with SQL Mobile / SQL Everywhere: altering the table schema. When you need to add or change columns to a table with all the data in, SQL CE Console makes a full copy of the source table to the target table while converting the necessary data (you may need to add msdadc.dll to your device, although this is not required on the desktop). So how are IDENTITY columns handled?
With SQL CE 2.0 there are no problems because the clever OLE DB provider allows you to overwite the generated value.
SQL Mobile, on the other hand, has a mean OLE DB engine - the only way you can influence the next IDENTITY value is by altering the column's seed through either a SQL command or a low-level OLE DB interface (my solution).
To avoid changing the IDENTITY seed for every copied row I use an optimization algorithm that keeps track of the next value and only alters the column if there is a difference between the value to write and the automatically generated one. This is actually a variation of the same algorithm found in Data Port Wizard.
For all you SQL CE Console users, here's the latest download.
Adventures in Desktop App Conversion
1 week ago