Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Sync Framework Question for SQL CE 3.5 and SQL Server 2008. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Team,


    I am currently working on a Distributed Application where users (when offline) have a local .sdf database on there machines and that data needs to be synchronized with the server database (.mdf file). Some master tables are Download Only and other tables are UploadOnly. The Download works but the Upload sometimes happen and sometimes not. Can you redirect me to some tutorials on the Sync framework that suits my scenario so that I can achieve this.

    I am using the "Local Data Cache" to generate the sync code in the backend and customizing the sync direction in the tables that are uploadOnly.

    Looking forward for your quick assistance.

    Thanks and Regards,

    Shalabh - s.shalabh4u@gmail.com

     

    Friday, July 1, 2011 9:29 AM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    check out this link:http://msdn.microsoft.com/en-us/library/cc761546(v=SQL.110).aspx

     

    Friday, July 1, 2011 9:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi June,

    Thanks for the reply.

    I tried to implement the post you referred me. I changed the SyncDirection from UploadOnly to BiDirectional and also removed "CreationDate" and "LastEditDate" from the insert and update statements in the TableSyncAdapter but still the client's changes(Inserts/Updates/Deletes) are not synchronizing with the server table.

    Can you please guide me further on this. Is there any way I can get some live assistance from you as its very urgent for me to implement by this week.

    Thanks in advance.
    Shalabh Gupta

    shalabh
    Friday, July 1, 2011 2:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    can you enable tracing? see: http://msdn.microsoft.com/en-us/library/cc807160.aspx

    you may also subscribe to ChangesSelected, ApplyChangesFailed events to see if changes are being selected and if changes being applied are failing...

    • Marked as answer by shalabhgupta Sunday, July 3, 2011 5:01 PM
    Friday, July 1, 2011 3:12 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi June,

    Thanks for your help, I managed to Upload the data to the server with your suggestion and its working properly now.

     

    Thanks again,

    Shalabh

     

    shalabh
    Sunday, July 3, 2011 5:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    Hope you are doing great.

    Need your help. In context to the above issue, As mentioned above, we have a Distributed Environment where multiple clients(SQL CE 1,2,3... and so on) would be using a common .sdf file that is shipped to them along with the application. The end user's would install the application on there local's and then there local changes would be syncd with the Server database (SQL Server 2008).

    --> As suggested by you, I have used the BiDirectional sync instead of UploadOnly and it worked for on client.

    --> I have also made a Unique Index on [GUID+Primary Key or GUID+UserID's] in client tables, so that each row is uniquely identified for a user.


    My concern are as follows:
    1) What additional changes should be done by me, so that the sync happens for multiple clients.
    2) Will the Sync Framework consider the Unique Index I created to make a row unique for each user automatically or do I need to mention these Indexes somewhere.

    I am not aware of the best practices in such scenerio, really need your assistance for the same.

    Thanks and Regards,
    Shalabh Gupta

     

     

    shalabh
    Monday, July 11, 2011 1:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    is the SDF you distribute to your client empty and only provisioned on first use?  if you have setup the SDF using the first client,meaning you've synched it with the server at least once, you might have problems simply copying it to the other clients. i may be wrong so test it by having two clients sync to the server and see that it syncs the changes. 

    on your second question, Sync Fx only knows about the PK and not about the unique index. when it syncs it will use the PK for uniqueness. can you not switch the PK to  [GUID+Primary Key or GUID+UserID's]? check the guidelines on choosing keys here: http://msdn.microsoft.com/en-us/library/bb726011(SQL.110).aspx

     

    Monday, July 11, 2011 2:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    Thanks for the reply.

    The .sdf we are shipping to the clients is a blank/clean .sdf file that is synced for the first time when the client registers itself and then the synchroniation is followed on consecutive login's.

    I have also cleaned the tables in this .sdf file where it stores the Anchor and Client Computer information so that the first sync autogenerates this information. Would this work in my scenario?

    I am now using [GUID+Primary Key] and [UserID+PrimaryKey] as PK and would be testing the sychronization against the same.

    Do I need to include something else in my process.

    Best Regards,
    Shalabh Gupta

     

     

     

    shalabh
    Tuesday, July 12, 2011 7:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    afaik, you should be good with your plan.
    Tuesday, July 12, 2011 7:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    Thanks. I have disabled and re-enabled the change tracking in the .sdf file programatically using:

    using (SqlCeChangeTracking changeTracking = new SqlCeChangeTracking(connection))
                                    {
                                        foreach (var syncTable in syncAgent.Configuration.SyncTables)
                                        {
                                            if (syncTable.TableName.Equals("TABLE1") || syncTable.TableName.Equals("TABLE2") || syncTable.TableName.Equals("TABLE3"))
                                            {
                                                ErrorLogging.LogMessages("Configured Table " + syncTable.TableName + " to Sync on GUID");
                                                changeTracking.DisableTracking(syncTable.TableName);
                                                changeTracking.EnableTracking(syncTable.TableName, TrackingKeyType.Guid, TrackingOptions.All);
                                            }
                                            else
                                            {
                                                ErrorLogging.LogMessages("Configured Table " + syncTable.TableName + " to Sync on Primary Key");
                                                changeTracking.DisableTracking(syncTable.TableName);
                                                changeTracking.EnableTracking(syncTable.TableName, TrackingKeyType.PrimaryKey, TrackingOptions.All);
                                            }
                                        }
                                    }

    Table1/2/3 have [GUID+AutoincrementID] as there primary key, so I have enabled changetracking on GUID. Even thou we have a Primary Key for these 3 tables, I am getting the following error:

    The table does not have a primary key. [ Table name = Table1] - SQL Server Compact ADO.NET Data Provider.

     

    Looking forward for your assistance..

    Best Regards,

    Shalabh Gupta

    shalabh
    Tuesday, July 12, 2011 11:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    if you are distributing a "clean" sdf, you dont have to manually enable Change Tracking. Sync Framework can do that for you via the table creation options.

    where are you getting the error above? during sync or during enabling of change tracking?

    Tuesday, July 12, 2011 12:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    During my testing, I got an error saying that I need to manually Disable and Enable the Change Tracking. So I implemented the above snippet. After implementing this, the changetracking is getting enabled for all the tables except 3 tables.

    I am getting this error when I am re-enabling the change tracking before I start Synchronization.

     

    BR/

    Shalabh

    shalabh
    Tuesday, July 12, 2011 12:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    TrackingKeyType.Guid is for a uniqueidentifier column that has the ROWGUIDCOL  attribute.  try just setting it to TrackingKeyType.PrimaryKey
    Tuesday, July 12, 2011 12:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Junet,

    Getting the following error after setting TrackingKeyType.PrimaryKey.

    The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking. - System.Data.SqlServerCe-    at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)
       at System.Data.SqlServerCe.SqlCeChangeTracking.IsTableTracked(String tableName)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.DisableOcsTracking(String tableName)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.DropOcsTable(String tableName)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateSchema(SyncTable syncTable, SyncSchema syncSchema)
       at Microsoft.Synchronization.SyncAgent.InitClientSchema()
       at Microsoft.Synchronization.SyncAgent.DataSynchronize()

    shalabh
    Tuesday, July 12, 2011 1:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    is your table creation option set to DropExistingOrCreateNewTable? if it is, then you dont have to manually disable and enable tracking. can you verify that your Sync Framwork and Sql CE version are compatible via this link: http://social.technet.microsoft.com/wiki/contents/articles/clarifying-sync-framework-and-sql-server-compact-compatibility.aspx

    Tuesday, July 12, 2011 2:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    Removed the Table Creation Option from DropExistingOrCreateNewTable to UseExistingTableOrFail.

    SQL CE Version : SQL CE 3.5, SP2

    Sync Framework Version: 1.0

    still getting the same error:

    The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking.

    shalabh
    Tuesday, July 12, 2011 3:28 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    try applying Sync Framework v1.0 SP1.
    Wednesday, July 13, 2011 1:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    Thanks, It resolved the issue with error message I was getting above, but this time I got a new error :(

    Unable to enumerate changes at the DbServerSyncProvider for table 'Table1' in synchronization group 'EUDataCacheGlobalSyncGroup'. - Microsoft.Synchronization.Data.Server-    at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
       at Microsoft.Synchronization.SyncAgent.DownloadChanges(SyncGroupMetadata groupMetadata)
       at Microsoft.Synchronization.SyncAgent.DataSynchronize()

    shalabh
    Wednesday, July 13, 2011 3:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

     

    I have resolved the above issue. ChangeTracking for some of the server tables were not enabled so it was throwing the error. I have enabled the changeTracking for these tables and it worked.

    The problem now is that for some of the tables that are connected via PK-FK relation are giving me this error while synching:

    A foreign key value cannot be inserted because a corresponding primary key value does not exist.

    Is it something to do with  the Table Creation Option that I have changed from DropExistingOrCreateNewTable to UseExistingTableOrFail.

    shalabh
    Wednesday, July 13, 2011 5:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    change the order you are adding the tables in the sync group to make sure the parent table comes first
    • Marked as answer by shalabhgupta Sunday, May 20, 2012 3:43 AM
    Wednesday, July 13, 2011 6:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This sorted me out as well thank you :)

    Here's what I did:

    1. Went to http://www.microsoft.com/en-us/download/details.aspx?id=17616
    2. Downloaded SyncSetup_en.x86.zip as our clients are on 32 bit machines and are english
    3. Extracted the zip files
    4. Ran the following on the client's machine: "SyncServicesADO.msi, Synchronization.msi, ProviderServices.msi"
    5. I suspect I don't need to run them all but due the lack of (or obscure) documentation around this from MS I ran them all
    6. Ran the app and the error "The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking." went away.
    Friday, May 18, 2012 4:17 PM