locked
Time out error while sync data between sql server 2008 and sql express 2008 using Microsoft sync framework RRS feed

  • Question

  • Dear All,

    I am using Microsoft sync framework for synchronization of data. My server is SQL Server 2008 and my client end is sql express 2008.

    I am using SyncAgent Class, SqlSyncAdapterBuilder Class and SqlServerChangeTracking system  for syncronization. Having near about 30 tables to sync. While syncronizing the data I am getting error like timeout period elapsed prior to completion of the operation or the server is not responding

     

    public void Synchronize(String strTableName)
        {
          
          DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();
          SqlExpressClientSyncProvider clientSyncProvider = new SqlExpressClientSyncProvider();
          
          SyncAgent syncAgent = new SyncAgent();
          syncAgent.RemoteProvider = serverSyncProvider;
          syncAgent.LocalProvider = clientSyncProvider;
    
          
          SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
          
          serverConnection = new SqlConnection(DL_Connection.ServerConnectionString());
          serverSyncProvider.Connection = serverConnection;
    
          clientConnection = new SqlConnection(DL_Connection.ConnectionString());
          clientSyncProvider.Connection = clientConnection;
    
          SyncGroup orderGroup = new SyncGroup("AllChanges");    
                
          SyncTable tableOrders = new SyncTable(strTableName);
          tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
          tableOrders.SyncDirection = SyncDirection.Bidirectional;
          tableOrders.SyncGroup = orderGroup;
          syncAgent.Configuration.SyncTables.Add(tableOrders);
          
          SqlSyncAdapterBuilder builderServer = new SqlSyncAdapterBuilder();
          builderServer.Connection = serverConnection;
          builderServer.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
          builderServer.SyncDirection = SyncDirection.Bidirectional;
          builderServer.TableName = strTableName;
          if (builderServer.TableName == "HO_Purchase_Header" || builderServer.TableName == "HO_Purchase_Line" || builderServer.TableName == "HO_Sales_Header" || builderServer.TableName == "HO_Sales_Line" || builderServer.TableName == "HO_Item_Ledger_Entry" || builderServer.TableName == "HO_Value_Entry" || builderServer.TableName == "HO_Requisition_Wksh_Name")
            builderServer.FilterClause = "SiteID = " + Site_Id.ToString();
         
          SyncAdapter ServerAdapter = builderServer.ToSyncAdapter();
          serverSyncProvider.SyncAdapters.Add(ServerAdapter);      
    
          
          SqlSyncAdapterBuilder Clientbuilder = new SqlSyncAdapterBuilder();
          Clientbuilder.Connection = clientConnection;
          Clientbuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
          Clientbuilder.SyncDirection = SyncDirection.Bidirectional;
          Clientbuilder.TableName = strTableName;
          if (Clientbuilder.TableName == "HO_Purchase_Header" || Clientbuilder.TableName == "HO_Purchase_Line" || Clientbuilder.TableName == "HO_Sales_Header" || Clientbuilder.TableName == "HO_Sales_Line" || Clientbuilder.TableName == "HO_Item_Ledger_Entry" || Clientbuilder.TableName == "HO_Value_Entry" || Clientbuilder.TableName == "HO_Requisition_Wksh_Name")
            Clientbuilder.FilterClause = "SiteID = " + Site_Id.ToString();
                
          
          SyncAdapter clientAdapter = Clientbuilder.ToSyncAdapter();
          
          clientSyncProvider.SyncAdapters.Add(clientAdapter);      
    
          SqlCommand anchroCommand = new SqlCommand
          {
            CommandText = "SELECT @"
            + SyncSession.SyncNewReceivedAnchor
            + " = change_tracking_current_version()"
          };
          anchroCommand.Parameters.Add("@"
             + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
             .Direction = ParameterDirection.Output;
          anchroCommand.Connection = serverConnection;
          anchroCommand.CommandTimeout = 0;
          serverSyncProvider.SelectNewAnchorCommand = anchroCommand;
          clientSyncProvider.SelectNewAnchorCommand = anchroCommand;      
    
          serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(serverSyncProvider_SyncProgress);
          serverSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(serverSyncProvider_ApplyChangeFailed);
          clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(clientSyncProvider_ApplyChangeFailed);
          
          SyncStatistics syncStats = syncAgent.Synchronize();      
        }
    

    Some time sync is successfully and some time timeout error. It mostly happen when sync record is more than 100 to 150 in one table

    Thanks.

     

    Wednesday, December 22, 2010 12:03 PM

Answers

All replies