none
Synchronization problem (MSSQL and SQL CE) RRS feed

  • Question

  • Hi,

    I have a problem with synchronization after a device hard reset. Now in one of the tables new records inserted on the device don't get synchronized. Currently the device is far away (1300 km) so I need to solve this problem with out it.

    I started investigating the issue on an emulator. I have databases (both MSSQL and SQL CE) and can see that for new records don't have any value in __sysChangeTxBsn or __sysInsertTxBsn column. Should them/one of them contain any value at all? During synchronization process debugging the table has 0 rows - checked in change selected event. 

    I thought that maybe tracking is off and I tried to enable tracking on this table but with no success. Is it possible that there might be missing any package on the device? Before hard reset everything was working correctly so maybe during application deployment some additional components were deployed from VS. How can I test it to check what wrong is? Any ideas?

    Thanks,

    xaze

    Sunday, November 24, 2013 5:00 PM

Answers

  • am not sure how you got this working before, but the provisioning code you posted above is for the newer sync provider which is not supported on WM devices.

    the only supported sync providers on WM are the SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent using the ADO.Net Synchronization Services for Devices

    • Marked as answer by xaze Sunday, December 15, 2013 3:02 PM
    Friday, December 6, 2013 2:46 PM
    Moderator

All replies

  • are you using a newly created client database? have you tried re-initializing the client DB?
    Thursday, November 28, 2013 1:27 AM
    Moderator
  • Yes, every time during synchronization new database is created and filled with data. So it's fresh.
    Saturday, November 30, 2013 11:10 AM
  • if you're recreating your database every time you sync, then you're wiping out every change you make on it.
    Monday, December 2, 2013 1:09 AM
    Moderator
  • It's recreated after data downloading.

    Sync process looks like this:

    1. Download data from SQL CE
    2. Backup database (SQL CE)
    3. Recreate database (SQL CE)
    4. Create provisioning (MSSQL, SQL CE).
    5. Upload data to SQL CE
    Monday, December 2, 2013 4:45 PM
  • a bit confused about your steps.

    how are you provisioning?

    which of the steps below is the local updates not getting synched? #1 or #5?

    Tuesday, December 3, 2013 2:18 AM
    Moderator
  • Synchronization doesn't work for downloading from SQL CE.

    To test the issue I:

    1. Create empty CE db
    2. Remove provisioning from MSSQL db.
    3. Run synchronization.
    4. Then CE database is used on the device (I add two rows).
    5. Run synchronization again but new rows are not downloaded.

    private void CreateProvision()
    {
    	for (int i = 0; i < tables.Length; i++)
    	{
    		CreateServerProvision(tables[i]);
    		CreateClientProvision(tables[i]);
    	}
    }
    
    private void CreateServerProvision(string tableName)
    {
    	// define a new scope named TableScope
    	DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(this.GetScope(tableName));
    
    	// get the description of the Products table from database
    	DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, serverConn);
    
    	// add the table description to the sync scope definition
    	scopeDesc.Tables.Add(tableDesc);
    
    	// create a server scope provisioning object based on the TableScope
    	SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
    
    	if (!serverProvision.ScopeExists(scopeDesc.ScopeName))
    	{
    		// skipping the creation of table since table already exists on server
    		serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    		if (useDiffrentScope.Contains(tableName))
    		{
    			switch (tableName)
    			{
    				case "PacklistLine":
    					serverProvision.Tables[tableName].AddFilterColumn("Export");
    					serverProvision.Tables[tableName].FilterClause = "[side].[Export] > 0";
    					break;
    
    				case "SalesLine":
    					serverProvision.Tables[tableName].AddFilterColumn("Closed");
    					serverProvision.Tables[tableName].FilterClause = "[side].[Closed] = 0";
    					break;
    
    				default:
    					break;
    			}
    		}
    
    		// start the provisioning process
    		serverProvision.Apply();
    	}
    }
    
    private void CreateClientProvision(string tableName)
    {
    	// get the description of TableScope from the server database
    	DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(this.GetScope(tableName), serverConn);
    
    	// create CE provisioning object based on the TableScope
    	SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
    
    	if (!clientProvision.ScopeExists(scopeDesc.ScopeName))
    	{
    		// if empty dest db uncomment this
    		clientProvision.PopulateFromScopeDescription(scopeDesc);
    		// starts the provisioning process
    		clientProvision.Apply();
    	}
    }

    Tuesday, December 3, 2013 11:38 PM
  • I wanted to test it again. Based on sync fx tutorial I created a console application synchronizing my database.

    When I try to add rows with the console test application __sysChangeTxBsn and __sysInsertTxBsn columns are filled with some values. But when I move the same database to emulator and insert a row those columns are empty. The same situation is on the real device. Those rows don't get synchronized:( What might cause that? Any ideas?

    I'll try to write a small app to insert row on the emulator.

    SalesLine rows

    I created a sample app for emulator executing the following query:

    cmd.CommandText = @"INSERT INTO [SalesLine]
                                   ([SalesId]
                                   ,[LineNum]
                                   ,[BaleId]
                                   ,[Price]
                                   ,[Closed]
                                   ,[Created]
                                   ,[Modified]
                                   ,[ID]
                                   )
                             VALUES
                                   ('igor911',
                                   3,
                                   2018142,
                                   20,
                                   0,
                                   '2013-12-05 22:23:00',
                                   NULL,
                                   '95691899-7B4E-166D-DCAE-0000F8270003')";

    And got the same result:| So it's not because of the wrong application code but there is a component missing on the device???

    SalesLine Table 2



    • Edited by xaze Thursday, December 5, 2013 10:21 PM
    Thursday, December 5, 2013 9:05 PM
  • have you checked SQL CE versions?
    Friday, December 6, 2013 1:32 AM
    Moderator
  • Assemblies are deployed with the application deployment. For the device I took assemblies from SQL CE installation directory. So they are the same.
    Friday, December 6, 2013 8:40 AM
  • What's your device OS btw? WM?
    Friday, December 6, 2013 10:41 AM
    Moderator
  • Windows CE 5.0
    Friday, December 6, 2013 10:55 AM
  • am not sure how you got this working before, but the provisioning code you posted above is for the newer sync provider which is not supported on WM devices.

    the only supported sync providers on WM are the SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent using the ADO.Net Synchronization Services for Devices

    • Marked as answer by xaze Sunday, December 15, 2013 3:02 PM
    Friday, December 6, 2013 2:46 PM
    Moderator
  • I copy database from the device to a computer and then synchronize.
    Sunday, December 8, 2013 10:35 PM
  • it will work on a desktop, but the sync providers you're using is not supported on WM 
    Monday, December 9, 2013 12:16 AM
    Moderator
  • Thank you JuneT for help! :)

    The problem was the latest Microsoft Synchronization Framework version. Using ADO synchronization helped. I also needed to re-add references to my project. 

    Sunday, December 15, 2013 3:06 PM