none
Microsoft Sync Framwork not showing any errors and not working RRS feed

  • Question

  • Hi,

        I am using SQL Express 2008 as both server and client and using microsoft sync framework 2.1 dll. This is the code in Synchronize method.

       Here ServerSyncProvider  is DbServerSyncProvider.dll and ClientSyncProvider is SqlExpressClientSyncProvider.dll (provided in some code sample)

     

         Dim serverSyncProvider As New DbServerSyncProvider()
            Dim clientSyncProvider As New SqlExpressClientSyncProvider()
            Dim syncAgent As New SyncAgent()

            syncAgent.RemoteProvider = serverSyncProvider
            syncAgent.LocalProvider = clientSyncProvider

            Dim builder As New SqlConnectionStringBuilder()
            builder("Data Source") = m_serverSqlInstanceName
            builder("integrated Security") = True
            builder("Initial Catalog") = m_serverDatabaseName
            serverConnection = New SqlConnection(builder.ConnectionString)
            serverSyncProvider.Connection = serverConnection

            builder.Clear()
            builder("Data Source") = m_clientSqlInstanceName
            builder("integrated Security") = True
            builder("Initial Catalog") = m_clientDatabaseName
            clientConnection = New SqlConnection(builder.ConnectionString)
            clientSyncProvider.Connection = clientConnection

     

            Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)

            Dim lbrClassSyncTable As New SyncTable("DailyLaborClass")
            lbrClassSyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail
            lbrClassSyncTable.SyncDirection = SyncDirection.Bidirectional

            Dim allGroup As New SyncGroup("AllChanges")
            lbrClassSyncTable.SyncGroup = allGroup

            syncAgent.Configuration.SyncTables.Add(lbrClassSyncTable)

            Dim lbrClassServerSyncAdapter As New SyncAdapter()
            Dim lbrClassServerBuilder As New SqlSyncAdapterBuilder(serverConn)
            lbrClassServerBuilder.TableName = "DailyLaborClass"
            lbrClassServerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
            lbrClassServerBuilder.SyncDirection = SyncDirection.Bidirectional
            lbrClassServerSyncAdapter = lbrClassServerBuilder.ToSyncAdapter
            lbrClassServerSyncAdapter.TableName = "DailyLaborClass"
            serverSyncProvider.SyncAdapters.Add(lbrClassServerSyncAdapter)

            Dim lbrClassClientSyncAdapter As New SyncAdapter()
            Dim lbrClassClientBuilder As New SqlSyncAdapterBuilder(clientConnection)
            lbrClassClientBuilder.TableName = "DailyLaborClass"
            lbrClassClientBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
            lbrClassClientBuilder.SyncDirection = SyncDirection.Bidirectional
            lbrClassClientSyncAdapter = lbrClassClientBuilder.ToSyncAdapter
            lbrClassClientSyncAdapter.TableName = "DailyLaborClass"
            clientSyncProvider.SyncAdapters.Add(lbrClassClientSyncAdapter)

            Dim selectNewAnchorCommand As New SqlCommand()
            Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
            With selectNewAnchorCommand
                .CommandText = _
                    "SELECT " + newAnchorVariable + " = change_tracking_current_version()"
                .Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
                .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
                .Connection = serverConn
            End With
            serverSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand
            clientSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand

         Dim syncStats As SyncStatistics = syncAgent.Synchronize()

     

        It is executing with out any errors. I updated a row in Server database through management studio and the change is not reflected on the client instance for the same table. I made sure that changetracking is enabled for both databases and both tables.

    Please help me out with this.

     

    Nagarjun.

     

     

     

     

     

     

     

     

     

    Tuesday, November 9, 2010 11:02 PM

Answers

  • I see that you're using DbServerSyncProvider and SyncAgent, but it sounds like you're just getting started using Sync Framework. These classes are for use only in existing applications and have been superseded by newer classes, like SqlSyncProvider and SyncOrchestrator. Here's a tutorial that can get you started using the latest classes to synchronize SQL Server and SQL Express: http://msdn.microsoft.com/en-us/library/ff928700(v=SQL.110).aspx
    Wednesday, November 10, 2010 8:35 PM

All replies

  • have you tried running SQL Profiler to capture the SQL statements and run them manually to check if it's returning rows?
    Wednesday, November 10, 2010 6:21 AM
    Moderator
  • Hi,

     I am using SQL Express and I am not sure whether SQL Express provides SQL Profiler. But before that I want to know whether Code is correct or not?

    Wednesday, November 10, 2010 2:16 PM
  • I see that you're using DbServerSyncProvider and SyncAgent, but it sounds like you're just getting started using Sync Framework. These classes are for use only in existing applications and have been superseded by newer classes, like SqlSyncProvider and SyncOrchestrator. Here's a tutorial that can get you started using the latest classes to synchronize SQL Server and SQL Express: http://msdn.microsoft.com/en-us/library/ff928700(v=SQL.110).aspx
    Wednesday, November 10, 2010 8:35 PM
  • Hi,

    Thanks for letting me know and I already started using them and making it work (it is deleting\updating\adding rows from server to client database correctly) but I have a problem

    when i add a column to a table and hit sync I am getting an error.

    My Code:

     Dim serverSqlDescription As New DbSyncScopeDescription("SyncTest ")
     Dim lbrclassDesc As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("LaborClass ", serverConn)

      Dim serverConfig As New SqlSyncScopeProvisioning(serverConn, serverSqlDescription)
      serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
      serverConfig.Apply()

     Dim clientSqlDescription As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("SyncTest ", serverConn)
     Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlConn, clientSqlDescription)
     clientSqlConfig.Apply()

    Here my scope name is SyncTest and TableName I am testing is LaborClass . If I add rows,delete rows, update rows on server and hit sync it is working fine.

     But when I add a whole new column to table LaborClass and hit sync, it is failing at clientsqlconfig.Apply() stating that 'column does not exist'. I know column does not exist on client database and need to update it when i hit sync.

     

     

     

     

    Wednesday, November 10, 2010 10:02 PM
  •  Any one more thing which I want to know was,

    even if I change one record on client side and hit sync,

    the statistics showed are

     Total changes Uploaded : 50

    Total changes Downloaded:50

    Will it just dump all the rows from server to client or client to server Or actually check for changes.

    For Ex: I have a table with 50 rows

    I deleted a row on client side and hit sync,my option is first upload then download

    So after sync,

    the statistics showed are

     Total changes Uploaded : 49

    Total changes Downloaded:50

    and it bring the row back from the database which is an error.

     

    I think I am clear with my question.

    How to rectify it??

     

    Wednesday, November 10, 2010 11:15 PM
  • From your description, it looks like each time ALL rows are enumerated and applied (i.e. you send ALL rows unregarding if they were modified or not).

    I bet if you do an empty sync (without any updates) you will see the same thing - 50 changes going up and 50 changes coming down.

    Another problem is that you seem not to enumerate the deletes (so in your example you deleted 1 row, but reported only the 49 rows you did not touch).

     

    Wednesday, November 10, 2010 11:22 PM
  • What modifications or options I need to add?
    Thursday, November 11, 2010 3:49 PM
  • are you provisioning your scope everytime you sync?
    Thursday, November 11, 2010 4:31 PM
    Moderator
  • Yes. I am doing like this every time.

     Dim serverSqlDescription As New DbSyncScopeDescription()

    serverSqlDescription = New DbSyncScopeDescription("SyncTest")

     Dim TestDesc As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable", serverConn)

    serverSqlDescription.Tables.Add(TestDesc)

     Dim serverConfig As New SqlSyncScopeProvisioning(serverConn, serverSqlDescription)
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip) 'skipping the creation of table since table already exists on server
                serverConfig.Apply()

     Dim clientSqlDescription As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("SyncTest", serverConn)
                Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlConn, clientSqlDescription)

     clientSqlConfig.Apply()

     Dim syncOrchestrator As SampleSyncOrchestrator = New SampleSyncOrchestrator(New SqlSyncProvider("SyncTest", clientSqlConn, Nothing), New SqlSyncProvider("SyncTest", serverConn, Nothing))
                Dim syncStats As SyncOperationStatistics = syncOrchestrator.Synchronize()

    The above code is called everytime I hit sync.

    Do i need to run these steps only once for the first time

     Dim serverConfig As New SqlSyncScopeProvisioning(serverConn, serverSqlDescription)
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip) 'skipping the creation of table since table already exists on server
                serverConfig.Apply()

    and read the scope from the next read onwards.

    Let me know.

     

     

    Thursday, November 11, 2010 4:41 PM
  • Never mind. I got it working.

    Thanks for your help...

     

    Thursday, November 11, 2010 6:36 PM
  • Is there any easy way of encrypting data while syncing?
    Thursday, November 11, 2010 7:27 PM