none
Unable to download Data from Server to Client while Sync in SQL server Databases

    Debate general

  • Hi,

    I am new to Microsoft Sync Framework and using below versions:

    Microsoft.Synchronization 2.1.0.0

    Microsoft.Synchronization.Data 2.0.0.0

    Microsoft.Synchronization.Data.Server 2.0.0.0

    I am trying to sync in 2 Sql Servers and not using SQl compact edition.

    I have written webservice for server side using 

    DbServerSyncProvider. and on client side I am implementing '

    ClientSyncProvider' and syncAgent. I am using SyncAdapter on both sides. When I called sunchronize() method - the client changes are easily uploaded at server but i m unable to download the changes veen i tried with 'downloadonly' also instead o fusing 'bidirectional'. I am handeling 'Applychangesfailed' event with

    DataTable clientchanges = e.Conflict.ClientChange;

    DataTable serverchanges = e.Conflict.ServerChange;

    e.Action =

    ApplyAction.RetryWithForceWrite;

     

    lunes, 12 de marzo de 2012 11:48

Todas las respuestas

  • am assuming you wrote a custom sync provider for the client side? or did you grab the publicly available SQLExpressClientSyncProvider?

    I suggest you use the newer SqlSyncProvider as only SQL CE is supported on the client side using the older offline providers (DbServerSyncProvider/SqlCEClientSyncProvider/SyncAgent combo)

    lunes, 12 de marzo de 2012 13:41
    Moderador
  • Hi JuneT,

    Thanks for reply.

    My client side code is as below:

    public class SQLClientProvider : ClientSyncProvider
    {
        // wraps a DbServerSyncProvider
        private DbServerSyncProvider _dbSyncProvider;
        public SQLClientProvider()
        {
            _dbSyncProvider = new DbServerSyncProvider();
            _dbSyncProvider.ApplyingChanges += new EventHandler<ApplyingChangesEventArgs>(_dbSyncProvider_ApplyingChanges);
            _dbSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(_dbSyncProvider_ApplyChangeFailed);
        }
    }

    public class Client
    {
        public void Synchronize()
        {
            SyncAgent _syncAgent = new SyncAgent();
            //SyncOrchestrator _syncOrchestrator = new SyncOrchestrator();

            //Remote Provider
            SyncProvider serverSyncProviderProxy;

            try
            {
                //ServerSyncProxy.Service synchronizerService = new ServerSyncProxy.Service();
                OfflineWebService.Synchronize synchronizerService = new OfflineWebService.Synchronize();
                serverSyncProviderProxy = new ServerSyncProviderProxy(synchronizerService);
                _syncAgent.RemoteProvider = serverSyncProviderProxy;

                //Client Provider                  
                string connString = _connString;// System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;

                SQLClientProvider clientSyncProvider = new SQLClientProvider();
                //for testing changing it to hardcoded one...
                IDbConnection localConnection = new SqlConnection(connString);

                clientSyncProvider.Connection = localConnection; ;
                _syncAgent.LocalProvider = clientSyncProvider;

                // Create SyncTables add to syncagent
                AddSyncTablesGroups(_syncAgent);

                //Define Adpaters for All tables
                AddSyncAdapters(clientSyncProvider);

                // select new anchor command
                SqlCommand anchorCmd = new SqlCommand();
                anchorCmd.CommandType = CommandType.StoredProcedure;
                anchorCmd.CommandText = "Sync_GetChangeTrackingCurrentVersion";
                anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt).Direction = ParameterDirection.Output;
                clientSyncProvider.SelectNewAnchorCommand = anchorCmd;

                SyncStatistics syncStats = _syncAgent.Synchronize();
            }
            catch (Exception ex)
            {
                SQLClientProvider.WriteToLog("syncronization ", ex.Message);
            }
        }
    }

    Actually now I am able to get the new rows from server to client but in case there is any upadtes in existing row at server side and when i click on syn, the client side rows override the changes at server side and I lost all my server changes :(

    Even I dont know how to handle the 'deleted rows'. I have sql server2008 and change tracking enabled so no tombstone tables etc.

    Please help me as this is very important and I m lagging behind my project schedule :(

    Thanks,

    Anu

    viernes, 16 de marzo de 2012 10:12
  • as i have mentioned, only SQL CE is supported as a client in the older offline provider. even the sample SQLExpressClientSyncProvider was already pulled out from the download site.

    I suggest you use the newer SQLSyncProvider.

    see: Tutorial:Synchronizing SQL Server and SQL Express

    viernes, 16 de marzo de 2012 12:22
    Moderador
  • Hi JuneT,

    which version of Microsoft Synchronization should I use to sync Sql server 2008 edition.

    Currently I m using Microsoft.Synchronization 2.1..0.0 & Microsoft.synchronization.data & data.server version 2.0.0.0

    Thanks,
    Anu

    I have enabled change tracking on database & tables.

    I have written sps & below code for each table:

    //SyncAdpater for ADT_AuditColumn Table

    SyncAdapter adADT_AuditColumn = new SyncAdapter("ADT_AuditColumn");

    //get rows of update conflict

    SqlCommand updateConflicts_ADT_AuditColumn = new SqlCommand();

    updateConflicts_ADT_AuditColumn.CommandType =

    CommandType.StoredProcedure;

    updateConflicts_ADT_AuditColumn.CommandText =

    "Sync_GetConflicts_AuditColumn";

    updateConflicts_ADT_AuditColumn.Parameters.Add(

    "@EntityID", SqlDbType.BigInt);

    adADT_AuditColumn.SelectConflictUpdatedRowsCommand = updateConflicts_ADT_AuditColumn;

    // select incremental inserts command

    SqlCommand getIncrementalInsserts_ADT_AuditColumn = new SqlCommand();

    getIncrementalInsserts_ADT_AuditColumn.CommandType =

    CommandType.StoredProcedure;

    getIncrementalInsserts_ADT_AuditColumn.CommandText =

    "Sync_GetIncrementalInserts_AuditColumn";

    getIncrementalInsserts_ADT_AuditColumn.Parameters.Add(

    "@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.UniqueIdentifier);

    adADT_AuditColumn.SelectIncrementalInsertsCommand = getIncrementalInsserts_ADT_AuditColumn;

    // insert row command

    SqlCommand insertincremental_ADT_AuditColumn = new SqlCommand();

    insertincremental_ADT_AuditColumn.CommandType =

    CommandType.StoredProcedure;

    insertincremental_ADT_AuditColumn.CommandText =

    "Sync_Insert_AuditColumn";

    insertincremental_ADT_AuditColumn.Parameters.Add(

    "@EntityID", SqlDbType.BigInt);

    insertincremental_ADT_AuditColumn.Parameters.Add(

    "@ColumnName", SqlDbType.VarChar, 200);

    insertincremental_ADT_AuditColumn.Parameters.Add(

    "@IsDefault", SqlDbType.Bit);

    insertincremental_ADT_AuditColumn.Parameters.Add(

    "@PageID", SqlDbType.Int);

    insertincremental_ADT_AuditColumn.Parameters.Add(

    "@ColumnOrder", SqlDbType.Int);

    adADT_AuditColumn.InsertCommand = insertincremental_ADT_AuditColumn;

    // select incremental updates command

    SqlCommand getIncrementalUpdate_ADT_AuditColumn = new SqlCommand();

    getIncrementalUpdate_ADT_AuditColumn.CommandType =

    CommandType.StoredProcedure;

    getIncrementalUpdate_ADT_AuditColumn.CommandText =

    "Sync_GetIncrementalUpdates_AuditColumn";

    getIncrementalUpdate_ADT_AuditColumn.Parameters.Add(

    "@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);

    adADT_AuditColumn.SelectIncrementalUpdatesCommand = getIncrementalUpdate_ADT_AuditColumn;

    ////update command

    SqlCommand updateIncremental_ADT_AuditColumn = new SqlCommand();

    updateIncremental_ADT_AuditColumn.CommandType =

    CommandType.StoredProcedure;

    updateIncremental_ADT_AuditColumn.CommandText =

    "Sync_Update_AuditColumn";

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@" + SyncSession.SyncForceWrite, SqlDbType.Int);

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@EntityID", SqlDbType.BigInt);

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@ColumnName", SqlDbType.VarChar, 200);

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@IsDefault", SqlDbType.Bit);

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@PageID", SqlDbType.Int);

    updateIncremental_ADT_AuditColumn.Parameters.Add(

    "@ColumnOrder", SqlDbType.Int);

    I forgot to mention that i m creating webservice at server side and cleint code need to be integrated in web application.

    adADT_AuditColumn.UpdateCommand = updateIncremental_ADT_AuditColumn;

    serverSyncProvider.SyncAdapters.Add(adADT_Audit);                  on server & client side and added tables to syncagent on client end. I have written corrosponding SPs also. now problem arises when row is updated on server side. i have to sync approx. 60 tables :( and I got stuck here. i m nopt in the condition to change much of code. help please


    • Editado AnuSumesh viernes, 16 de marzo de 2012 13:53
    viernes, 16 de marzo de 2012 13:29
  • either version should work with SQL 2008.
    lunes, 19 de marzo de 2012 2:19
    Moderador
  • hi JuneT,

    Row update at client end is getting reflected at server end but if i update any row at server end and then sync then client side did not get affectde with server changes. Only new rows inserted at server end are insertde at client end while sync but issue is there whne updateing.

    Any help?

    Thanks,

    Anu

    lunes, 19 de marzo de 2012 11:33
  • as i have mentioned, you're better off using the newer provider as the client/server provider combo you are trying is not supported.

    in any case, if you want to debug, subscribe to the ChangesSelected event of the server provider so you can isolate if its actually picking up the updates. you can then subscribe to the ApplyChangesFailed event on the client to check if it's the change application that is failing.

    you may want to re check your selectincrementalupdates stored proc as well. better, run sql profiler to see the actual query with parameters.

    lunes, 19 de marzo de 2012 13:25
    Moderador
  • Hi JuneT,

    One issue that I found is : whenever I update the row of table and tried to track the updated changes by using following command:

    SELECT

    * FROM CHANGETABLE(CHANGES <tablename>,

    0

    ) CT where CT.SYS_Change_Operation='U'    I get blank even row is upadated. it happens for both server & client. Finally the client side code is always applied at server end. whenever I sync - it sync all the rows even those rows are updated earlier also. Any clue? i m using following sp to get the currentversion : PROCEDURE [dbo].[Sync_GetChangeTrackingCurrentVersion]-- Add the parameters for the stored procedure here

    @sync_new_received_anchor

    bigint output --binary(2048)

    BEGIN

    AS

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT @sync_new_received_anchor = change_tracking_current_version();

    Return @sync_new_received_anchor;

    END    ANd ClientAnchor table at client end with sentAnchor & ReceievdAnchor, Tablename columns where datatype for anchor is set to Binary[2048].  Please help.  ANU

      

    ALTER


    Anu

    martes, 20 de marzo de 2012 7:28
  • when you do an update, what does these return?

     

    SELECT * FROM CHANGETABLE(CHANGES <tablename>, 0)

    or

    SELECT * FROM CHANGETABLE(CHANGES <tablename>, null)

    martes, 20 de marzo de 2012 8:48
    Moderador
  • Hi JuneT,

    Thanks. I was using same but logic in ApplyChanges was wrong. Now it worked fine for me updates & deletes too. But still i need to test it real scenario. Till now i tested it on my local DB by creating 2 DBs and was trying to sync those. I used below code at server side:

    private

    void serverSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)

    {

    //DataTable clientchanges = e.Conflict.ClientChange;

    //DataTable serverchanges = e.Conflict.ServerChange;

    e.Action =

    ApplyAction.RetryWithForceWrite;

    }

     

    I need to log the sync failures. Can you please tell me how can I get the excat reason for failure and table names where failure occurs and while insert or update or delete & time etc. So that we can shop to user that these tables did not sync so that he can try again.

    Thanks a lot for your help.

    Anu


    Anu

    martes, 20 de marzo de 2012 14:01
  • the event argument should be able to give you all of that, the table, the conflicting rows, the type of conflict, etc...

    its all in the documentation that gets installed with the framework.

    you'll find the same here: How to: Handle Data Conflicts and Errors

    this one is for the newer providers, but the concept around conflict handling should be the same: How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server)

    miércoles, 21 de marzo de 2012 1:32
    Moderador
  • Hi JuneT,

    I am again in trouble. When I was trying to sync 2 databases and both were on my local system and webservice was also running on my local system, then sync on both sides worked fine. But today when i tried to test it in real scenario i.e. as follows:

    WebService : System1
    application Server: system1
    Database server: system2
    ClientMAchine:system3
    In this scenario, I am able to get the server details at client end but when there are changes at client end and trying to sync - its saying that "NO CHANGES" even though the data is changed at client end and even inserted new rows. But unable to detect changes of client machine.

    Can you please help me? :(

    Thanks,
    Anu


    Anu

    jueves, 22 de marzo de 2012 11:18
  • did you just copy the client database from you machine to system 3?  try creating an empty client database instead...
    jueves, 22 de marzo de 2012 11:23
    Moderador
  • I have common script to create DB that i used on server & client machine. I have inserted data for some lookup tables at cleint end. And then when I sync - all server data is downloaded successfully on client DB. Then I made changes at client DB and tried to sync but its saying "No changes" found. Agian tried with server changes and synced successfully. Only client changes are not getting reflected.


    Anu

    jueves, 22 de marzo de 2012 11:31
  • why dont you try running the select * from changetable(...) to see if its actually detecting the changes...or subscribe to ChangesSelected event on the client?

    jueves, 22 de marzo de 2012 11:49
    Moderador
  • I tried with  select * from changetable() with

    SYS_CHANGE_OPERATION = 'U' but its not returning rows. And when i tried with

    SYS_CHANGE_OPERATION='I' then its returning rows with updated values at client end. But is not detecting in changes. Even for server changes for

    SYS_CHANGE_OPERATION='U', nothing detecetd.

    Any idea?

    Anu


    Anu

    jueves, 22 de marzo de 2012 13:17
  • check you change tracking retention period.

    again, as i previously mentioned, you'll be better off using the newer SqlSyncProvider. given the amount of time you've spent on this custom client provider for the older offline provider, you would have been done by now if you migrated and with less issues around synching. even the previously downloadable sqlexpressclientsyncprovider was pulled out from the public download.

    jueves, 22 de marzo de 2012 13:23
    Moderador
  • Actually my seniors are stick to this as no one has idea abt sync & no one worked on this earlier.

    Retention period is 5days

    Anu


    Anu

    jueves, 22 de marzo de 2012 14:14
  • i dont understand why the query with the Change tracking table will return nothing for an update but return something for an insert not unless the metadata has been cleaned up. The SQL Change Tracking is a feature of SQL  Server and thas is not even part of Sync Framework. If you have problems with change tracking, then its just logical that Sync Framework is not able to retrieve changes.

    can you try disabling all change tracking and re-enabling them.

    jueves, 22 de marzo de 2012 15:37
    Moderador
  • Hi JuneT,

    after long struggle I am able to sync my data (creates problem sometimes only).

    But now i have one more issue : adding refernce of web service to my project. When I add web service refernce in my class application, it creates "Web References" folder and I can see Refernce.cs file there. But when i add web refernce in my web site, it creates "App_WebReferences" folder and no refernce.cs is there. I need Reference.cs because i need to edit this file as given in tutorials "

  • Edit the Reference.cs manually and add namespace Microsoft.Synchronization.Data. Also remove code for types that are already defined in Microsoft.Synchronization.Data.dll as the sample reference.cs file shows "  in http://www.syncguru.com/projects/SyncServicesDemoWebService.aspx.
  • How can I achieve same in Web Site?
  • Also in case i change web service URL in future, will it affect the Refernce.cs also?
  • How can we resolve these issues as we want to have web service URL to be dynamic?

    Thanks,

    Anu


  • Anu

martes, 27 de marzo de 2012 3:56
  • can you not have the web service url in the config and just set it before you instantiate the service?
    jueves, 05 de abril de 2012 10:31
    Moderador
  • Hi JuneT,

    Thanks a lot for your help till now. But  I am unable to sync Data in bidirectional using old framework :-(

    Now I am trying the new framework as suggsetd by you during first discussion and its synching the data.

    But in that scenario, we are facing one issue that we need server database connection string at client end for client provisioning & then for sync purpose.

    I used web service for server DB provisioning and written a webmethod to return the connection string to client.

    Issue is - if Database not on public IP  then I am unable to coonect to DB and getting following error:

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

    I tested it by disabeling browser settings for server database instance.

    Is there any other way to deal with this scenario?

    Thanks,

    Anu


    Anu

    viernes, 06 de abril de 2012 6:57
  • you can generate a snapshot sdf to initialize your client or  if the client is sqlexpress/sqlserver, do a back up of the server db, restore to client and run post-restore fixup...that should take care of provisioning...

    see: http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserverce.sqlcesyncstoresnapshotinitialization.generatesnapshot.aspx and

    http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserver.sqlsyncstorerestore.performpostrestorefixup.aspx

    for sync, you can setup the sync via wcf, see: http://code.msdn.microsoft.com/Database-SyncSQL-Server-e97d1208 or http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab

    viernes, 06 de abril de 2012 11:50
    Moderador