locked
Unable to do initial sync over WCF RRS feed

  • Question

  • I have a SQl CE Database that I am able to sync with my Sql Server Express database over WCF on my local IIS.

    I have now created a new, empty database on a remote server that I can connect to via WCF, but I am unable to sync my client with this new database.

    I get the following exception:

    Exception has been thrown by the target of an invocation.

    -Inner Exception:

         Unable to enumerate changes at the DbServerSyncProvider for table 'Stations' in synchronization group 'StationsSyncTableSyncGroup'

    I have read up on a lot of forum posts and MSDN articles explaining initial synchronization (for example here) and reinitializing client databases due to change tracking information having been cleaned up (even though my error message doesn't explicitly state that that was the problem).

    I tried recreating my client database before sync and also specifying "UploadExistingOrCreateNewTable" as the table create option (especially because I have other tables on my client that I would not want to lose),

    but I can't get it to sync with my server. What am I doing wrong? Can anyone point me in the right direction?

    Any help is greatly appreciated.

    Monday, August 22, 2011 9:38 AM

Answers

  • when you created a new empty database on the remote database, are you expecting sync to re-create the tables based on the client tables? the provider and syncagent you're using will only create the tables being synched on the client side and not the other way around.

    assuming you re-created the tables you're synching on the new remote database, did you enable change tracking if you're using SQL Change Tracking? The Local Database Cache Wizard does this for you, but if you have simply created a new database and re-directed your code to use that DB, you need to enable the change tracking yourself.

    to help you troubleshoot, i suggest you enable Sync Framework tracing. in addition, you can use Sql Profiler to see what commands are being sent to the remote database, copy and run them yourself and see what they return.

    • Marked as answer by CWinks Sunday, September 4, 2011 12:49 PM
    Tuesday, August 23, 2011 1:11 AM
  • I tried setting things up again with no change.

    I got it to work on my local IIS by enabling the user "ASPNET" on SqlServer, so then I knew that it must be some specific configuration that wasn't ok on my server.

    And finally I found that even though Change Tracking was enabled on the Database as a whole, it wasn't enabled on each and every table! After I enabled change tracking on each database table sync finally went through.

    Thanks for your help, once again, JuneT!

     

    • Marked as answer by CWinks Sunday, September 4, 2011 12:49 PM
    Sunday, September 4, 2011 12:49 PM

All replies

  • I tried installing the client application on the server machine and syncing from the same machine. It didn't help, I still get the same error. Has anyone encountered this before or does anyone know what I could try?

    Thanks

    Monday, August 22, 2011 5:53 PM
  • when you created a new empty database on the remote database, are you expecting sync to re-create the tables based on the client tables? the provider and syncagent you're using will only create the tables being synched on the client side and not the other way around.

    assuming you re-created the tables you're synching on the new remote database, did you enable change tracking if you're using SQL Change Tracking? The Local Database Cache Wizard does this for you, but if you have simply created a new database and re-directed your code to use that DB, you need to enable the change tracking yourself.

    to help you troubleshoot, i suggest you enable Sync Framework tracing. in addition, you can use Sql Profiler to see what commands are being sent to the remote database, copy and run them yourself and see what they return.

    • Marked as answer by CWinks Sunday, September 4, 2011 12:49 PM
    Tuesday, August 23, 2011 1:11 AM
  • Thanks for your tips.

    The new database on the server has the same schema as the original one, but is just empty of any data, except for a few rows I added as a test.

    I enabled Change Tracking.

    Question: Could it be that I need to "reinitialize" the client database because the change tracking information has been cleaned up? (Or maybe because there is no change tracking information to begin with?) How do I do that in my scenario?

    I will see what I can do with Sync Framework tracing and Sql Profiler

    Any further ideas though?


    Tuesday, August 30, 2011 6:39 AM
  • yes, you need to reinitialize your client db. when you originally synched your client, it stored a timestamp from the original server you synch with in its LastReceived anchor.

    Now when you sync it with the new server, it is using that timestamp to check for changes in the new server.

    if you look at the code for the selectincremental commands you will find that it is comparing the last received anchor to the Change Tracking timestamp and is actually raising an error if it finds that the client stored last received anchor is outdated based on the Change Tracking timestamp.

    this is what differentiates the offline provider with the collaboration provider, in that the client can only sync with the server it originally initialized its sync. not unlike the collaboration providers where you can introduce a new sync partner/replica anytime.

    Tuesday, August 30, 2011 7:11 AM
  • Ah, that makes sense.

    Could you tell me how to reinitialize the client db?

    Tuesday, August 30, 2011 7:23 AM
  • just create a new empty SDF.

    but then you have to repopulate either your SDF or your new remote DB with your existing data.

    Tuesday, August 30, 2011 7:42 AM
  • I created the empty SDF ans saw that it was trying to initialize synchronization - finally...

    However, I got the following fault:
      Inner Exception Found:
      Type: System.ServiceModel.FaultException`
      Message: Unable to initialize the client database, because the schema for table '<list of all table names>' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Make sure that you can establish a connection to the client database and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly.

    On the server OnInitialized() method I do the following to simulate "uploadOnly" for one of my tables based on JuneT's blog: http://jtabadero.wordpress.com/2010/03/26/configuring-local-database-cache-generated-sync-for-uploadonly/

            StempelSyncAdapter.SelectIncrementalInsertsCommand = Nothing
            StempelSyncAdapter.SelectIncrementalUpdatesCommand = Nothing
            StempelSyncAdapter.SelectIncrementalDeletesCommand = Nothing

    And on the client LocalDataCacheClientSyncProvider_CreatingSchema method I make some schema changes adding newid() GUIDs to the defaultvalue of some primary key columns

    Otherwise I don't do anything with the schemas. What could be the problem?

    (Another question: I enabled Tracing on the client - which works fine, but on the server it doesn't - how can I enable it?)

    Thanks!

    Tuesday, August 30, 2011 10:58 AM
  • if i remember it right, the schema is based on the table structure returned by the selectincremental command. so dont null it out.

    also, use the sync direction to set the direction of the synchronization.

    Tuesday, August 30, 2011 11:11 AM
  • Yes, I tried not nulling it out - there was no difference.

    And what do you mean by set the direction of the synchronization - should I change what I have set until now?

    What can I do about my problem though - do you have any other ideas why it can't download my schema from the client?

    And what about tracing sync on the server - why doesn't it work there? Is it because IIS doesn't allow writing to files? How can I get tracing on the server?

    Many thanks, this whole thing is taking way longer than I expected...

     

    Tuesday, August 30, 2011 6:37 PM
  • afaik, sync framework uses the structure of the result of the selectincremental commands to get the schema of the server table so it can apply it on the client side (this is when you dont use the Local Database Cache designer to initialize the client table)

    when you set the commands to null, it has no way of figuring out the structure.

    if you want to set the sync direction to upload, there is sync direction property for each table in your sync group.

    the code in the blog post your referenced is if you initialize using the Local Database Cache Designer. Now if you're initializing from code, you should at least run your sync once with the selectincremental commands set and then null them afterwards.

    Wednesday, August 31, 2011 1:29 AM
  • Thanks for the explanation - I see now that initializing from the local Database Cache and from code is different.

    I tried doing what you suggested - not nulling out the selectIncremental commands but no difference yet.

    I really want to see what's going on on the server - how do I enable tracing there?

    Thursday, September 1, 2011 6:06 AM
  • did you set the sync direction to download first?
    Thursday, September 1, 2011 10:32 AM
  • I just got a little closer to what I hope is the solution, although I still have to figure it out.

    I tried syncing with an empty SDF on my local IIS and got the same error.

    So I tried going backwards and instead of using WCF as a service, I just ran it in the local ServiceHost.

    And here I got a much "friendlier" Error Message

    Object of type 'System.String[]' cannot be converted to type 'System.Collections.ObjectModel.Collection`1[System.String]'.

    With Stacktrace:

       at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr)
       at System.Reflection.MethodBase.CheckArguments(Object[] parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at Microsoft.Synchronization.Data.ServerSyncProviderProxy.GetSchema(Collection`1 tableNames, SyncSession syncSession)
       at Microsoft.Synchronization.SyncAgent.InitClientSchema()
       at Microsoft.Synchronization.SyncAgent.Synchronize()
       at TimeClient.clsWohnplusTimeSyncManager.sync(String r_strArbeitsstation, String& r_strUserDisplayMessage) in C:\Documents and Settings\Chana\My Documents\Visual Studio 2008\Projects\WohnplusTime\TimeClient\clsWohnplusTimeSync.vb:line 54

    The following post helped me realize that somewhere between my service and the client there is a conversion problem when creating a new SDF:

    http://social.microsoft.com/Forums/en/syncgeneral/thread/6701225d-6c92-4f00-92e6-a8dec352827e

    I see that in my service the "GetSchema" has a Collection(String) as a parameter:

        Public Overridable Function GetSchema(ByVal tableNames As Collection(Of String), ByVal syncSession As SyncSession) As SyncSchema Implements IWTSLocalDataCacheSyncContract.GetSchema
            Return Me._serverSyncProvider.GetSchema(tableNames, syncSession)
        End Function

    But where does the conversion happen? Please help me solve this!

    Thank you

    Thursday, September 1, 2011 10:38 AM
  • but isnt all of this running perfectly fine until you tried to  connect to a new remote database?

    if you use Add Web Reference to add the service reference, i think there are options there on how to map collections.

    Thursday, September 1, 2011 10:53 AM
  • It was running perfectly fine locally, until I tried reinitializing the database through code - I had never done that. I was just using a DB that was initialized through the local data cache. It crashed on me when I tried to sync with the remote database, but probably not because something was wrong with my code, but because of this conversion problem.

    Please help me figure out what to do.

    How do I change the mapping of collections when I use a service reference, but also when I use the local WCF Host??

    Thursday, September 1, 2011 11:07 AM
  • i dont think it has something with the service if it used to work before.

    how about you just try to reinitialize the local db using the Local Database Cache wizard again?  just double click on the .sync file and reconfigure the connection strings to point to your databases.

    Thursday, September 1, 2011 11:46 AM
  • I cannot assume it worked before because I never tried this particular scenario - I never even tried syncing an empty SDF with an empty DB.

    Thursday, September 1, 2011 3:36 PM
  • HELP! I don't know what to try anymore, I'm running out of ideas what to look for.

    Can you suggest a way of debugging, tracing or anything that would give me more information about my problem?

     

    Thursday, September 1, 2011 7:18 PM
  • i suggest you start fresh (follow this:  Programming Microsoft Synchronization Services for ADO.NET (Devices))

    and just apply your customization afterwards.

    am not sure how you ended up with  Collection(Of String) instead of System.String[]. my proxy shows as a string[] as well.

    you can try changing the Collection(Of String) to String[] instead and see if that works.

    Friday, September 2, 2011 1:31 AM
  • I will try to set the whole thing up again and see if there's a difference.

    Thanks for your suggestions!

    Friday, September 2, 2011 10:51 AM
  • I tried setting things up again with no change.

    I got it to work on my local IIS by enabling the user "ASPNET" on SqlServer, so then I knew that it must be some specific configuration that wasn't ok on my server.

    And finally I found that even though Change Tracking was enabled on the Database as a whole, it wasn't enabled on each and every table! After I enabled change tracking on each database table sync finally went through.

    Thanks for your help, once again, JuneT!

     

    • Marked as answer by CWinks Sunday, September 4, 2011 12:49 PM
    Sunday, September 4, 2011 12:49 PM