Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Data from Sql CE not getting synced to Sql Server DB RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    When I insert a row in CE, I don't see any corresponding entry in <Table>_tracking table and after that when I run sync, the data does not get synced to Sql Serve DB.

    I believe its happening because the changes on CE are not getting tracked.

    Any idea, what may be the cause of this probem and the solution for that.


    Thanks
    Sumit
    Wednesday, March 3, 2010 5:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    So, to answer your question, simply copying CE files to another machine does not work.

    You need to use SqlCeStoreSnapshotInitialization API to correct the CE 2 metadata to become a new client. THis way, changes made from CE 1 and CE 2 do not

    conflict with one another. i.e. CE 2 needs to be understood as a different client from CE 1.

    Patrick
    • Marked as answer by Sumit J Friday, March 12, 2010 6:49 PM
    Friday, March 12, 2010 5:45 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    I would be glad to help. On CE, local changes are not tracked on the tracking table but on the user table internal columns.

    So, it is normal that you don't get tracking entries unless you have sync'd to it.

    Can you subscribe to ApplyingChanges event on destination provider and take a look at the enmerated data set?

    You might also want to look at SyncProgressStatistics to observe any changes.

    If there is no data change being sent to destination, it might mean that the source CE database is not provisioned correctly.

    Thanks,

    Patrick

    Wednesday, March 3, 2010 7:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Patrick,

    Thanks for your reply.

    I have tried by adding subscribing to ApplyingChanges event on server (destination in this case). Though the application reaches there, but the data set is empty and no exception is thrown. So I am not sure whether the CE database is wrongly provisioned or some other problem is there.



    Thanks
    Sumit 
    Wednesday, March 3, 2010 8:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    When you provisioned the CE database, if it was not empty, the existing rows will not be tracked and will not be synced. The current design assumption is that CE database is empty at provisioning time since it is a client storage.

    If the row is inserted after provisioning the CE database, it should be synced. Please enable tracing (http://msdn.microsoft.com/en-us/library/cc807160(SQL.105).aspx) for your managed application. It will give your more information for what has happened. You can share it to us if you need further help.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, March 4, 2010 1:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    My CE was not empty when I provisioned it, but I don't want this initial data to sync back to sql server DB. So I guess that part is ok.

    The problem was for the rows that were inserted after provisioning the CE, which as you said should be synced were not getting synced.


    Somehow it is working now, when I opened the CE file with another version of Microsoft SQL Server Compact Edition mentioned at(http://support.microsoft.com/kb/974068/en-us)  and just saved the file. After that I provisioned it again and tried sync and it worked.
     
    It means that the problem was definitely with the provisioning, but I still don't know what was the actual problem.

    Does this information gives any idea?

    Thanks
    Sumit
    Thursday, March 4, 2010 4:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It makes sense. This is a SQL CE bug for PK-FK handling. We expect customers to use the latest SQL CE 3.5 SP1 QFE build to avoid this issue. When you insert such kind of rows in the CE database with the previous version of SQL CE, did you get that error?

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, March 4, 2010 9:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    hi Dong,

    I already had 3.5.5692.0 version installed on my machine, so when I was installing the hotfix I got error that latest version of CE is already installed on the machine. Therefore, I uninstalled the version installed on my machine and then installed the hotfix. After that things worked. With the original version I was getting the PK-Fk error.

    My question now is if I already had the latest version then why the PK-FK problem was there or is the hotfix latest than the 3.5.5692.0 version? Can you tell me how do I get SQL CE 3.5 SP1 QFE build?

    thanks
    Sumit
    Friday, March 5, 2010 3:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    the latest hotfix is 3.5.5692.14. Check it out here: http://support.microsoft.com/kb/974068

    it just got added in the announcement part of this forum above.
    Friday, March 5, 2010 3:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,


    I have taken the hotfix from the same site which you mentioned, but while installing this I get error that latest version is already installed on my machine, so I am not able to continue further. However, if I leave at this point then the original PK-FK still remains.

    So is there a way to install the hotfix without complaining about latest version already installed on the machine?

    Thanks
    Sumit
    Friday, March 5, 2010 3:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    The hotfix file version is 3.5.5692.14 that is newer than 3.5.5692.0. In order to install it, you need to uninstall the previous installed SQL CE instance.

    Thanks,
    Dong

    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, March 10, 2010 5:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote


    Hi,

    Thanks Dong.

    Earlier I thought that by installing the hotfix I was able to resovle the problem in uploading the data from CE to Sql server, but the problem still persists. I see that when two CE clients are syncing with same Sql server then both the clients are not able to upload teh data tp server, however download works fine.

    The steps that I followed are:

    1) insert a row in CE1
    2) sync (bidirectional) CE1 with Sql Server DB -- this works fine -- row gets uploaded to server
    3) insert a row in CE2
    4) sync (bidirectional) CE2 with Sql Server DB -- this does not work -- row does not get uploaded to server
    5) insert a row in CE1 again
    6) sync (bidirectional) CE2 with Sql Server DB -- now this also does not work -- row does not get uploaded to server

    at this point both the clients have stopped uploading the data to server. However, if I had not used CE2 and just continued with CE1 things would be OK. So that means that the problem is definitely with multiple clients syncing to same server.



    Since earier Patrick pointed that the problem may be related to provisioning, so I am describing the details of how I have provisioned my CE and Server. If I am doing something wrong then please point that out.


    server provisioning

     

    if (syncGroup != null)
    {
    serverScopeDesc =     new DbSyncScopeDescription(syncGroup.Name);
    serverScopeDesc.Tables.Add(    SqlSyncDescriptionBuilder.GetDescriptionForTable(syncGroup.Name, ServerConnection));
    serverProvisioner =     new SqlSyncScopeProvisioning(serverScopeDesc);
    serverProvisioner.SetCreateTableDefault(    DbSyncCreationOption.Skip);
    File.AppendAllText(ConfigurationHelper.GetApplicationSettingValue(ServerSyncInfrastructureScriptKey), "\n");
    File.AppendAllText(ConfigurationHelper.GetApplicationSettingValue(ServerSyncInfrastructureScriptKey), serverProvisioner.Script(ServerConnection.Database), System.Text.Encoding.ASCII);
    }



    Client provisioning

     

    if (syncGroup != null)
    {
    clientScopeDesc =     new DbSyncScopeDescription(syncGroup.Name);
    clientScopeDesc.Tables.Add(    SqlSyncDescriptionBuilder.GetDescriptionForTable(syncGroup.Name, ServerConnection));
    clientProvisioner =     new SqlCeSyncScopeProvisioning(clientScopeDesc);
    if (!clientProvisioner.ScopeExists(syncGroup.Name, LocalConnection))
    {
    clientProvisioner.SetCreateTableDefault(    DbSyncCreationOption.Skip);
    clientProvisioner.Apply(LocalConnection);
    }
    }


    Thanks
    Sumit

    Thursday, March 11, 2010 9:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    A few things that you may want to check:

    1. Since you create SyncScopeDescription for server and client separately, did you check if the Sql Server and CE have the same table schema? Is the SyncScopeDescription sgenerated from server and client side are same?
    2. Can you also check if the two SyncScopeDescription of the two CE databases are same?
    3. If the 2 CE databases are not on the same machines, please check if the CE hotfix is installed on both machines.
    4. In your code, you create offline TSQL script with the provisioning APIs. You did successfully applied this script on your SQL Server, right?

    If everything looks right to you still, please share us the trace log. 

    Thanks,
    Dong

    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, March 11, 2010 10:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    Is it possible that you are using different scope name for CE2?

    If your intention is to provision using different scope name between Server - CE2, then you need to provision server for the second scope.

    Otherwise, if you are using same scope name, this should work.

    Let me know if you have made any progress. It seems that CE2 has the problem.

    Patrick
    Thursday, March 11, 2010 10:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Dong,

    1. I have checked that both server and CE have same schema for all the tables.

    2. Since CE2 is just a copy of CE1, therefore it should have same SyncScopeDescription , right?

    3. 2 CEs are on different machines, but both have the hotfix installed.

    4. I have run the TSQL script on server and it did not give any error.

    So everything looks fine to me, and I am not getting any exception and the log which I am tracing is following:

    2010-03-11 17:44:04,860 INFO  - Sync Starting for Sync Group Company:
    -----------------------------------------------------------------------------------------------------------------

    2010-03-11 17:44:06,095 INFO  - Sync Completed for Sync Group Company: Took '1.2343829' seconds to complete


    I would guess that such scenario must be a normal usecase for sync where two different clients will try to sync with server. So I am not able to understand what is the missing part in my code.

    Hi Patrick,

    I am using the same scope name for both the CEs, again because CE2 is just a copy of CE1. Still its not working.
    Any other idea?

    Thanks
    Sumit

    Thursday, March 11, 2010 11:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    there is a small change in the behavior that I am observing:

    Earlier I wrote that after CE2 fails to upload, CE1 also starts failing upload, which is not correct. CE1 still works fine. so only CE2 is failing, which is surprising because teh database is just a copy of CE1 and running on different machine.

    Similarly if I create another copy of CE1 say CE3 and use it with the same application on 3rd machine then CE3 is also unable to upload.

    I can see a pattern here, which I don't know makes some sense or not.
    -- The very first client which tries to upload on server is the only one who can upload, the rest are unable to do so.

    Thanks,
    Sumit
    Thursday, March 11, 2010 11:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sumit,

    you copied the CE database before the provisioning or after the provisioning? If after provisioning, you should use SqlCeSyncStoreSnapshotInitialization class to create the copy.

    Thanks,
    Dong

    This posting is provided AS IS with no warranties, and confers no rights.
    Friday, March 12, 2010 12:59 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Dong.

    I provisioned CE1 and then just copied it for another client.

    Now from your answer, it looks to me that I was taking the completely wrong approach in distributing CEs.

    Let me provide brief explaination how I am using these CEs.


    My client code uses CE and just interacts with the server, not with other clients. And I have several such clients who interacts with the server. So I thought that I needed to provision only one CE and then I could distribute that CE to all the clients just by simply copying. Is this not the right way?

    Could you please give more details on what is the best way to sync CE and distribute it to multiple clients in such cases.

    Thanks
    Sumit
    Friday, March 12, 2010 2:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sumit,

    So, to answer your question, simply copying CE files to another machine does not work.

    You need to use SqlCeStoreSnapshotInitialization API to correct the CE 2 metadata to become a new client. THis way, changes made from CE 1 and CE 2 do not

    conflict with one another. i.e. CE 2 needs to be understood as a different client from CE 1.

    Patrick
    • Marked as answer by Sumit J Friday, March 12, 2010 6:49 PM
    Friday, March 12, 2010 5:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Patrick.


    I have tried this and it resolved the problem.
    Friday, March 12, 2010 6:49 PM