Environment:
SQL Server 2000 sp3a Std Edition
SQL Server CE 2.0
I recently made some schema changes to a SQL Server Merge Replication that
resulted in the entire SQL CE database schema getting initialized (same as an
initial snapshot). When the synchronization was initiated from the CE device
data changes did not get merged; instead all data changes were lost on the CE
device and a new database was created with the schema changes.
I didn't expect to lose data off the CE device; I expected the data to be
merged with the publisher and the schema changes to be applied to the
subscriber.
Obviously I did some wrong.
Here are the basic steps I made to apply schema changes to an existing
publication:
1. I created two new database tables on the sql server using t-sql 'create
table ...' commands. Primary keys and rowguid columns were added during the
create.
Tables created were called 'manufacturer' and 'voltage'.
2. I modified an existing published article by adding a number of columns.
I used the following SP and syntax to add the columns:
sp_repladdcolumn Entity_Nameplate, Transformer_Size, "int null"
sp_repladdcolumn Entity_Nameplate, Primary_Connections, "int null"
sp_repladdcolumn Entity_Nameplate, Primary_Configuration, "int null"
sp_repladdcolumn Entity_Nameplate, PCB_Level, "int null"
sp_repladdcolumn Entity_Nameplate, Oil_Capacity, "int null"
sp_repladdcolumn Entity_Nameplate, Weight, "int null"
sp_repladdcolumn Entity_Nameplate, Secondary_Configuration, "int null"
sp_repladdcolumn Entity_Nameplate, Manufacturer_Id, "int null"
sp_repladdcolumn Entity_Nameplate, Date_Manufactured, "datetime null"
sp_repladdcolumn Entity_Nameplate, Serial_Number, "int null"
sp_repladdcolumn Entity_Nameplate, Primary_Voltage_Id, "int null"
sp_repladdcolumn Entity_Nameplate, Secondary_Voltage_Id, "int null"
3. I added foreign keys for columns Entity_Nameplate.Manufacturer_Id
(child) to reference parent table 'manufacturer' created in step 1 above.
I added foreign keys for child records Entity_Nameplate.Primary_Voltage_Id,
entity_Nameplate.Secondary_Voltage_Id to reference parent 'voltage' table
created in step 1.
4. Then I added the new created tables step 1 to the existing publication as
articles. I used SQL Server Enterprise Manager i.e. publication wizard to
add tables 'manufacturer' and 'voltage' to the publication.
5. I added row filters to the publication for the two new articles using
SQL Server Enterprise Manager i.e. publication wizard.
6. The last step I performed was the running of the snapshot.
Based on the above the merge replication initiated a complete
reinitialization when the CE device was synched.
Is there something obvious I did wrong?
