locked
Unable to enumerate changes at the DbServerSyncProvider error RRS feed

All replies

  • have you made the corresponding entry for "MyTestTable" in the anchor table?
    Wednesday, March 24, 2010 8:52 PM
  • Yes, I entered "MyTestTable" into "TableName" column

    Thursday, March 25, 2010 3:50 PM
  • Are you synchronizing databases between SQL Express server and SQL server?  If so, you may want to look at the below sample (WCF does not necessarily have to be involved).  You may want to use SqlSyncProvider (which is a part of the Sync Framework V2 release) to do sync.

    Database Sync - SQL Server and SQL Server Express
    http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3762

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 25, 2010 8:39 PM
    Answerer
  • Thank you for suggesion but I can run the downloaded sample without any error. (Local: SQL 2008 express, Remote: SQL 2000)

    Once I change the table from "order" to "MyTestTable", the error below occured.

    "Unable to enumerate changes at the DbServerSyncProvider for table 'MyTestTable' in synchronization group 'AllChanges'."

    The error is located at syncengine.vb, last line of sub synchronize as below.

     

    Dim syncStats As SyncStatistics = syncAgent.Synchronize()  //error is here

     

     

     

     

    Thursday, March 25, 2010 9:09 PM
  • did you add the corresponding sync adapter for the table?
    Thursday, March 25, 2010 10:16 PM
  • Yes, I changed code below (including store procedure in SQL server)


    Dim adapterOrders As New SyncAdapter("orders")
    ' select incremental inserts command
    Dim incInsOrdersCmd As New SqlCommand()
    incInsOrdersCmd.CommandType = CommandType.StoredProcedure
    incInsOrdersCmd.CommandText = "sp_orders_incrinserts"
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8)
    adapterOrders.SelectIncrementalInsertsCommand = incInsOrdersCmd
    Dim insOrdersCmd As New SqlCommand()
    insOrdersCmd.CommandType = CommandType.StoredProcedure
    insOrdersCmd.CommandText = "sp_orders_applyinsert"
    insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int)
    insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
    adapterOrders.InsertCommand = insOrdersCmd

    to

    Dim adapterOrders As New SyncAdapter("mytesttable")
    ' select incremental inserts command
    Dim incInsOrdersCmd As New SqlCommand()
    incInsOrdersCmd.CommandType = CommandType.StoredProcedure
    incInsOrdersCmd.CommandText = "sp_mytesttable_incrinserts"
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
    incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8)
    adapterOrders.SelectIncrementalInsertsCommand = incInsOrdersCmd
    Dim insOrdersCmd As New SqlCommand()
    insOrdersCmd.CommandType = CommandType.StoredProcedure
    insOrdersCmd.CommandText = "sp_mytesttable_applyinsert"
    insOrdersCmd.Parameters.Add("@testid", SqlDbType.Int)
    insOrdersCmd.Parameters.Add("@testdate", SqlDbType.DateTime)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
    insOrdersCmd.Parameters.Add("@" & SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
    adapterOrders.InsertCommand = insOrdersCmd

    Friday, March 26, 2010 1:13 PM
  • One more question.

    I really do not understand how to use the anchor table for initial.

    What I did in my project is creating table Anchor, input "MyTestTable" into [TableName] column and left [SentAnchor]

    [ReceivedAnchor] blank.

    Am I right?

     

    Friday, March 26, 2010 1:44 PM
  • it's ok to leave the sentanchor and receivedanchor blank, syncfx will take care of assigning the values after the sync.

    am assuming you created the table on the client side/server side as well.

    when you get the error, can you check the innerexception as well?

     

    Saturday, March 27, 2010 1:08 AM
  • How to check the innerexception?

    I followed the way from http://msdn.microsoft.com/en-us/library/hdwz4c0s(VS.80).aspx

    try

    ...
        catch ex As System.IO.IOException
        MsgBox(ex.InnerException)
    End Try

    but got the same error as below:

    "Unable to enumerate changes at the DbServerSyncProvider for table 'MyTestTable' in synchronization group 'AllChanges'."

    The error is located at syncengine.vb, last line of sub synchronize as below.

    Dim syncStats As SyncStatistics = syncAgent.Synchronize()  //error is here

    Monday, March 29, 2010 12:48 PM
  • Here is my Synchronize(). To make debug simple, I only test one table. I double checked store procedures, they are all changed accordingly.

    I spent one week but still no luck.

    (Remote: SQL 2000, Local: SQL 2008 express.)

    Public Sub Synchronize()

                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") = serverSqlInstanceName_Renamed
                builder("Initial Catalog") = serverDatabaseName_Renamed
                builder("User ID") = "sa"
                builder("Password") = "srvpass"
                serverConnection = New SqlConnection(builder.ConnectionString)
                ' serverConnection = New SqlConnection(cConnStrSVR)
                serverSyncProvider.Connection = serverConnection
                                              
                builder.Clear()
                builder("Data Source") = clientSqlInstanceName_Renamed
                builder("Initial Catalog") = clientDatabaseName_Renamed
                builder("User ID") = "sa"
                builder("Password") = "cltpass"
                clientConnection = New SqlConnection(builder.ConnectionString)
                clientSyncProvider.Connection = clientConnection

                Dim tableOrders As New SyncTable("mytesttable")
                tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
                tableOrders.SyncDirection = SyncDirection.UploadOnly

                Dim orderGroup As New SyncGroup("AllChanges")
                tableOrders.SyncGroup = orderGroup

                syncAgent.Configuration.SyncTables.Add(tableOrders)
                Dim adapterOrders As New SyncAdapter("orders")

                Dim incInsOrdersCmd As New SqlCommand()
                incInsOrdersCmd.CommandType = CommandType.StoredProcedure
                incInsOrdersCmd.CommandText = "sp_scr_incrinserts"
                incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
                incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
                incInsOrdersCmd.Parameters.Add("@" & SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8)

                adapterOrders.SelectIncrementalInsertsCommand = incInsOrdersCmd

                Dim insOrdersCmd As New SqlCommand()
                insOrdersCmd.CommandType = CommandType.StoredProcedure
                insOrdersCmd.CommandText = "sp_scr_applyinsert"
                insOrdersCmd.Parameters.Add("@id", SqlDbType.Int)
                insOrdersCmd.Parameters.Add("@scrdate", SqlDbType.DateTime)
                insOrdersCmd.Parameters.Add("@" & SyncSession.SyncClientIdHash, SqlDbType.Int)
                insOrdersCmd.Parameters.Add("@" & SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)
                insOrdersCmd.Parameters.Add("@" & SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output

                adapterOrders.InsertCommand = insOrdersCmd

                Dim updcftOrdersCmd As New SqlCommand()
                updcftOrdersCmd.CommandType = CommandType.StoredProcedure
                updcftOrdersCmd.CommandText = "sp_scr_getupdateconflict"
                updcftOrdersCmd.Parameters.Add("@id", SqlDbType.Int)

                adapterOrders.SelectConflictUpdatedRowsCommand = updcftOrdersCmd

                Dim delcftOrdersCmd As New SqlCommand()
                delcftOrdersCmd.CommandType = CommandType.StoredProcedure
                delcftOrdersCmd.CommandText = "sp_scr_getdeleteconflict"
                delcftOrdersCmd.Parameters.Add("@id", SqlDbType.Int)

                adapterOrders.SelectConflictDeletedRowsCommand = delcftOrdersCmd

                serverSyncProvider.SyncAdapters.Add(adapterOrders)

                AddClientSyncAdapters(clientSyncProvider)

                AddHandler serverSyncProvider.SyncProgress, AddressOf serverSyncProvider_SyncProgress
                AddHandler serverSyncProvider.ApplyChangeFailed, AddressOf serverSyncProvider_ApplyChangeFailed
                AddHandler clientSyncProvider.ApplyChangeFailed, AddressOf clientSyncProvider_ApplyChangeFailed
                Dim syncStats As SyncStatistics = syncAgent.Synchronize()

            End Sub

    Monday, March 29, 2010 1:17 PM
  • For dubug, I added "MyTestTable" into downloaded sample (SQL Express - Client Synchronization Sample (C# v2.0 CTP1) ) and changed some codes accordingly.

    I got an error once click "Sync" button:

    Table name 'MyTestTable' is not valid. This might be caused by one or more of the following issues:  unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF.

    and stop at

    Function GetChanges() AT SqlExpressClientSyncProvider.vb

      Dim context As SyncContext = _dbSyncProvider.GetChanges(groupMetadata, syncSession)

    Monday, March 29, 2010 7:59 PM
  • check that you replaced the table name inside AddClientSyncAdapters as well. the code you have changed so far is on the server side adapter.
    Tuesday, March 30, 2010 12:25 AM
  • I checked AddClientSyncAdapters. It changed as well.

    I guess that problem is database.

    Does orders, orders_tombstone, anchor and guid four tables requir some setup to sync?

    Tuesday, March 30, 2010 7:28 PM
  • have you checked the schema name for your table? (dbo.tablename or [someotherschemaname].tablename)

    to confirm what statement is getting sent to SQL server, you can run SQL Profiler and look at the SelectIncremental stored procs being sent to SQLServer, copy them and run them in SQL Query Analyzer to check if they execute correctly.

    Wednesday, March 31, 2010 2:52 AM