locked
Records deleted in SQLCe is not propagated to the remote server upon Sync RRS feed

  • Question

  • Hi All

     

    I am facing an issue where by the records deleted on SQL CE is not being propagated to the remote server. Any help to solve this is really appreciated.

     

    I am using Sync services to Sync a SqlCe database witha a remote SQL Server.

    On the client, I created a new record in a table, and did a Sync, and the new record appeared in the remote Server. So far so good

    Then as a next step, I deleted the record from SQL Ce and ran the Sync again, but the deleted record never got removed from the Server.

     

    Upon doing more digging with the help of SQL profiler, I found that, upon Sync after the record is deleted from SQL CE, the sql statements send to the remote server is wrong. Sync framework is sending the value of the key that was deleted as null.

     

    Pasted below is the SQL Statement that i found in the SQL server profiler upon executing the Sync after deleting the record.

     

    declare @p4 int
    set @p4=0
    exec sp_executesql N'DELETE FROM [Country] WHERE ([CountryId] = @CountryId) SET @sync_row_count = @@rowcount',N'@CountryId bigint,@sync_row_count int output',@CountryId=NULL,@sync_row_count=@p4 output
    select @p4
    go

     

    As you can see, Sync framework is sending the value of CountryID as null, which ofcourse wont delete anything from the server. In addition to finding out why this is happening, I would like to get some insight as to how the framework gains the knowledge that it should use CountryID as the key when constructing these SQL statements? CountryId is not the primary key in this table, the primary key of this table is a guid. More over, delete on the table from the code was nto done on CountryID, it was done on CountryCode, [Delete from country where countrycode = 'abc']

     

    I checked SQLCE and found the reference to the deleted record in the __sysOCSDeletedRows table, with a varbinary value for _sysRK column and with the delete and Insert TXCSn and Deletedtime columns properly fileld in.

     

    Few more details on the Sync Settings.

     

    SyncTable syncTable = new SyncTable(tableName);

    syncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;

    syncTable.SyncDirection = SyncDirection.Bidirectional;

    syncTable.SyncGroup = syncGroup;

     

    SqlSyncAdapterBuilder adapterBuilder = new SqlSyncAdapterBuilder(connection);

    adapterBuilder.TableName = tableName;

    adapterBuilder.SyncDirection = SyncDirection.Bidirectional;

    SyncAdapter adapter = adapterBuilder.ToSyncAdapter();

    adapter.TableName = tableName;

    //Created IncrementalInsertsCommand

    //Created IncrementalUpdatesCommand

    //Created IncrementalDeletesCommand

    adapter.SelectIncrementalInsertsCommand = IncrInserts;

    adapter.SelectIncrementalUpdatesCommand = IncrUpdates;

    adapter.SelectIncrementalDeletesCommand = IncrDeletes;

     

    Thanks again to all.

     

    Shahin

    • Moved by Hengzhe Li Friday, April 22, 2011 5:59 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, January 22, 2009 11:06 PM

All replies

  • Shahin,

     

    Could you provide more details on the schema of the Country table and if there are any constraints or other attributes on the CountryId column?  Also, how is the primary key on the GUID column defined?

     

    Thanks-

    Friday, January 23, 2009 4:48 PM
  •  

    Hi Phil

     

    Thanks for the reply.

     

    Here are the details of the country table in SQL Ce.

    CREATE TABLE Country

    (

    CountryId bigint NOT NULL ,

    CountryCode nvarchar(20) NULL ,

    CountryName nvarchar(255) NULL ,

    CountrySyncId uniqueidentifier NOT NULL default NEWID()

    )

    go

     

    ALTER TABLE Country ADD CONSTRAINT XPKCountry PRIMARY KEY (CountrySyncId)

    go

    CREATE UNIQUE NONCLUSTERED INDEX XUKCountry ON Country(CountryId ASC )

    go

     

    There are many other tables that are dependent on the CountryID. But in the case I was testing, the row was brand new, and hence no other tables were refering to it and I was deleting it almost immediately after the creation.

     

    Please let me know if you need any more info.

    Thanks much

    Shahin

    Friday, January 23, 2009 10:05 PM
  • Shahin-

     

    I tried this on my machine and got a different result where CountrySyncId was correctly used as the PK.  Here's what I did, could you try the same and see what happens?

     

    1) Created your County table based on the script you gave in your previous post

    2) Ran the simple adapterBuilder code below:

     

    // Define a conn to your local store

    conn.Open();

    string tableName = "Country";

    SyncTable syncTable = new SyncTable(tableName);

    syncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;

    syncTable.SyncDirection = SyncDirection.Bidirectional;

    SqlSyncAdapterBuilder adapterBuilder = new SqlSyncAdapterBuilder(conn);

    adapterBuilder.TableName = tableName;

    adapterBuilder.SyncDirection = SyncDirection.Bidirectional;

    SyncAdapter adapter = adapterBuilder.ToSyncAdapter();

    Console.WriteLine("del cmd: " + adapter.DeleteCommand.CommandText);

    conn.Close();

     

    The output to the console is:

     

    del cmd: DELETE FROM [Country] WHERE ([CountrySyncId] = @CountrySyncId) SET @sync_row_count = @@rowcount

     

    This correctly identified CountrySyncId as the Key column.  Could you give this a shot and post back with the output?

     

    Thanks-

    Monday, January 26, 2009 7:03 PM