CODEWITHSUNDEEP
c#sqlsql-serversynchronizationmicrosoft-sync-framework
I A Khan
I A Khan

Reputation: 8859

Microsoft Sync Framework : Cannot enumerate changes at the RelationalSyncProvider for table 'Table Name'

have look at below codes

Here is my _cSynchronization Class where Sync Function are precent, (500) in connection string means timeout = 500

public static class _cSynchronization
    {
        public static int transactionCount;
        public static uint BatchSize = 10000;
        public static uint MemorySize = 20000;

        public static List<string> _MGetAllTableList()
        {
            List<string> list = new List<string>();
            DataRowCollection _dr = _CObjectsofClasses._obj_CDatabase._MGetDataRows("Select TABLE_NAME From INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME <> N'AUTOBACKUPSET' AND TABLE_NAME <> N'BINDATA' AND TABLE_NAME <> N'_ATTENDANCESTATUS' AND TABLE_NAME NOT like '%_tracking%' AND TABLE_TYPE ='BASE TABLE' AND TABLE_NAME <> N'schema_info' AND TABLE_NAME <> N'scope_info' AND TABLE_NAME <> N'scope_config'  AND TABLE_NAME <> '_CLIENTNAME'  AND TABLE_NAME <> '_TABSETTING' AND TABLE_NAME <> '_EMPLOYEEPAYMENT1' AND TABLE_NAME <> '_LOCALCOMPANYINFO' ORDER BY TABLE_NAME");
            int a = 0;
            string x = "";
            if (_dr.Count > 0)
            {
                _CPubVar._value_I = 0;
                _CPubVar._MaxValue_I = _dr.Count + 2;
                _CPubVar._IsTableProcess_bool = true;
                foreach (DataRow _row in _dr)
                {
                    _CPubVar._value_I++;
                    _CPubVar._ProcessText_S = "Preparing Tables " + _CPubVar._value_I + " of " + _CPubVar._MaxValue_I;
                    x = _CObjectsofClasses._obj_CConvert._MConvertToString(_row[0]);
                    //  serverConn.Open();
                    list.Add(x);
                }
            }
            return list;
        }
        public static void SetUp(string _pTableName)
        {
            // Connection to  SQL Server database
            SqlConnection serverConn = new SqlConnection(_CObjectsofClasses._obj_CConnectionString._MGetServerConnectionString(500));
            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(_CObjectsofClasses._obj_CConnectionString._MGetConnectionString(500));


            // Create a scope named "product" and add tables to it.
            DbSyncScopeDescription productScope = new DbSyncScopeDescription(_pTableName + "_SCOP");

            // Select the colums to be included in the Collection Object

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable(_pTableName,serverConn);
            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);
            serverProvision.ObjectSchema = ".dbo";

            //
            serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);

            if (!serverProvision.ScopeExists(_pTableName + "_SCOP"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);


            if (!clientProvision.ScopeExists(_pTableName + "_SCOP"))
                clientProvision.Apply();

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
        }
        public static List<_CSyncDetails> Synchronize(string _pScopeName, SyncDirectionOrder _pDirection)
        {
            // Connection to  SQL Server database
            SqlConnection serverConn = new SqlConnection(_CObjectsofClasses._obj_CConnectionString._MGetServerConnectionString(500));

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(_CObjectsofClasses._obj_CConnectionString._MGetConnectionString(500));

            List<_CSyncDetails> _Statics = new List<_CSyncDetails>();

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider(_pScopeName, serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider(_pScopeName, clientConn);


            clientProvider.CommandTimeout = serverProvider.CommandTimeout = 500;
            //Set memory allocation to the database providers
            clientProvider.MemoryDataCacheSize = serverProvider.MemoryDataCacheSize = MemorySize;


            //Set application transaction size on destination provider.
            serverProvider.ApplicationTransactionSize = BatchSize;


            //Count transactions
            serverProvider.ChangesApplied += new EventHandler<DbChangesAppliedEventArgs>(RemoteProvider_ChangesApplied);


            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = _pDirection;

            // Create SyncOperations Statistics Object

            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
            _Statics.Add(new _CSyncDetails { UploadChangesTotal = syncStats.UploadChangesTotal, SyncStartTime = syncStats.SyncStartTime, DownloadChangesTotal = syncStats.DownloadChangesTotal, SyncEndTime = syncStats.SyncEndTime });

            // Display the Statistics

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
            return _Statics;

        }
  }

Here the function where I am sync

 private void _MSync()
        {

        _CPubVar._IsContinue = true;
        _CPubVar._PausebtnCondition = 0;
       // _cSynchronization._MClearSyncprovision();

        _CPubVar._Stop_bool = false;

        this.Text += " - Started at : " + DateTime.Now;
        string a = ""; 
        // Define the Products table.
        List<string> _Tablelist = new List<string>();
        Collection<string> _ColNames = new Collection<string>();
        _list1.Add(new _CSyncDetails { SyncStartTime = DateTime.Now });

        _Tablelist.AddRange(_cSynchronization._MGetAllTableList());

        SyncDirectionOrder _order = SyncDirectionOrder.Download;
        _CPubVar._MaxValue_I = (_Tablelist.Count * 2);
        _CPubVar._value_I = 0;
        foreach (string tbl in _Tablelist)
        {
            try
            {
                a = Regex.Replace(Environment.MachineName + Environment.UserName, @"[^0-9a-zA-Z]+", "").ToUpper() + "_" + tbl + "_SCOPE";
                _CPubVar._value_I++;
                _CPubVar._ProcessText_S = "Sync Tables " + _CPubVar._value_I + " of " + _CPubVar._MaxValue_I;   
                _cSynchronization.SetUp(tbl);

                if (_CPubVar._IsServerRunning_bool)
                {
                    _order = SyncDirectionOrder.DownloadAndUpload;
                }
                else
                {
                    if (tbl == "_BANK" || tbl == "_BANKACCOUNT" || tbl == "_CLIENTNAME" || tbl == "_PACKAGE" || tbl == "_PACKAGEDET" || tbl == "_PAYMENTEXPENCES" || tbl == "_PROJECT" || tbl == "_PROJECTDET" || tbl == "_REQUIREMENT" || tbl == "_REQUIREMENTDET" || tbl == "_SERVER" || tbl == "_UNIT" || tbl == "_ITEM" || tbl == "ManageUser" || tbl == "USERPERMISSION" || tbl == "USERROLE" || tbl == "USERROLEDET")
                    {
                        _order = SyncDirectionOrder.DownloadAndUpload;
                    }
                    else
                    {
                        _order = SyncDirectionOrder.Download;
                    }
                }
                _CPubVar._value_I++;
                _CPubVar._ProcessText_S = "Sync Tables " + _CPubVar._value_I + " of " + _CPubVar._MaxValue_I;


                if (tbl != "_COMPANYINFO")
                {
                    _list1.AddRange(_cSynchronization.Synchronize(tbl + "_SCOP", _order));
                }
                else
                {
                    if (_CPubVar._IsServerRunning_bool)
                    {
                        _list1.AddRange(_cSynchronization.Synchronize(tbl + "_SCOP", SyncDirectionOrder.DownloadAndUpload));
                    }
                }
            }
            catch (Exception exx)
            {
                _syncErr.Add(new _CSyncErrors { SyncErrorDateTime = DateTime.Now, SyncErrorMessage = exx.Message, SyncTableAlies = _CTableName._MgetTableAlies(tbl) });
                pictureBox1.Visible = label3.Visible = true;
                label3.Text = _syncErr.Count.ToString();
                Application.DoEvents();
                continue;
            }
        }
        thread.Abort(); 

    }

Problem :

Above codes are working fine for only one PC Sync at a time (let Take A) there is no error and done.

one PC Sync at a time (let Take B) there is no error and done.

But when I am trying to run Application simultaneously (A and B Same time) then for some table I am getting

Cannot enumerate changes at the RelationalSyncProvider for table 'TableName'

Running Status

PC A      PC B      Result
YES        NO       No Error
NO         YES      No Error
YES        YES      Error

Please Note That On Client side MY database is 2008 and Server side MY database is 2012 Where I am wrong

UPDATE : I have 72 tables in database and below Error is not specific 'TableName' it may be any table from 72,

For Example Table1 gives me Error, and after Sync done if I rerun application may be this error not come.

Cannot enumerate changes at the RelationalSyncProvider for table 'TableName'

Upvotes: 1

Views: 3546

Answers (1)

Shaun Luttin
Shaun Luttin

Reputation: 141662

Check the Timeout

Eight minutes might not be enough time. Try increasing the synchronization command timeout to an absurd number to find out if that's the problem.

 clientProvider.CommandTimeout = 3000;
 serverProvider.CommandTimeout = 3000;

Turn on Tracing

Edit the app.config file for your application by adding the following system.diagnostics segment. It will log verbosely to C:\MySyncTrace.txt.

<configuration>
  <system.diagnostics>
    <switches>
      <!--4-verbose.-->
      <add name="SyncTracer" value="4" />
    </switches>
    <trace autoflush="true">
      <listeners>
        <add name="TestListener"
             type="System.Diagnostics.TextWriterTraceListener"
             initializeData="c:\MySyncTrace.txt"/>
      </listeners>
    </trace>
  </system.diagnostics>
</configuration>

My Recreation

I tried to recreate the error that you are experiencing. I created a simplified version of what you are trying to accomplish. It builds and successfully synchronizes two SqlExpress databases.

Unfortunately, I wasn't able to recreate the error. Here is the setup that I used and the test cases afterwards.

Mock Databases Create Script

USE Master;
GO

IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'SyncTestServer'
        )
    DROP DATABASE SyncTestServer
GO

CREATE DATABASE SyncTestServer;
GO

CREATE TABLE SyncTestServer.dbo.Table1 (Column1 VARCHAR(50) PRIMARY KEY)
CREATE TABLE SyncTestServer.dbo.Table2 (Column1 VARCHAR(50) PRIMARY KEY)

INSERT INTO SyncTestServer.dbo.Table1 (Column1)
VALUES ('Server Data in Table1')

INSERT INTO SyncTestServer.dbo.Table2 (Column1)
VALUES ('Server Data in Table2')

IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'SyncTestClient'
        )
    DROP DATABASE SyncTestClient
GO

CREATE DATABASE SyncTestClient;
GO

CREATE TABLE SyncTestClient.dbo.Table1 (Column1 VARCHAR(50) PRIMARY KEY)
CREATE TABLE SyncTestClient.dbo.Table2 (Column1 VARCHAR(50) PRIMARY KEY)

INSERT INTO SyncTestClient.dbo.Table1 (Column1)
VALUES ('Client Data in Table1')

INSERT INTO SyncTestClient.dbo.Table2 (Column1)
VALUES ('Client Data in Table2')

Mock Console Application

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization;

namespace StackOverflow_SyncFramework
{
    public class _CSyncDetails
    {
        public int UploadChangesTotal;
        public DateTime SyncStartTime;
        public int DownloadChangesTotal;
        public DateTime SyncEndTime;
    }

    public class Program
    {
        static void Main(string[] args)
        {
            _cSynchronization sync = new _cSynchronization();
            sync._MSync();

            Console.ReadLine();
        }
    }

    public class _cSynchronization
    {
        public static int transactionCount;
        public static uint BatchSize = 10000;
        public static uint MemorySize = 20000;
        public const string ServerConnString = 
            @"Data Source=.\SQLExpress;initial catalog=SyncTestServer;integrated security=True;MultipleActiveResultSets=True;";
        public const string ClientConnString = 
            @"Data Source=.\SQLExpress;initial catalog=SyncTestClient;integrated security=True;MultipleActiveResultSets=True;";

        public static List<string> _MGetAllTableList()
        {
            // I just created two databases that each have the following table
            // Synchronization is working
            List<string> list = new List<string>()
            {
                "Table1",
                "Table2"
            };
            return list;
        }

        public static void SetUp(string _pTableName)
        {
            // Connection to  SQL Server database
            SqlConnection serverConn =
                new SqlConnection(ServerConnString);
            // Connection to SQL client database
            SqlConnection clientConn =
                new SqlConnection(ClientConnString);

            // Create a scope named "product" and add tables to it.
            Console.WriteLine(_pTableName);
            DbSyncScopeDescription productScope = new DbSyncScopeDescription(_pTableName + "_SCOP");

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable(_pTableName, serverConn);
            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);
            serverProvision.ObjectSchema = ".dbo";

            serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);

            if (!serverProvision.ScopeExists(_pTableName + "_SCOP"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);

            if (!clientProvision.ScopeExists(_pTableName + "_SCOP"))
                clientProvision.Apply();

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();
        }

        public static List<_CSyncDetails> Synchronize(string _pScopeName, SyncDirectionOrder _pDirection)
        {
            // Connection to  SQL Server database
            SqlConnection serverConn =
                new SqlConnection(ServerConnString);

            // Connection to SQL client database
            SqlConnection clientConn =
                new SqlConnection(ClientConnString);

            List<_CSyncDetails> _Statics = new List<_CSyncDetails>();

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider(_pScopeName, serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider(_pScopeName, clientConn);

            clientProvider.CommandTimeout = serverProvider.CommandTimeout = 500;
            //Set memory allocation to the database providers
            clientProvider.MemoryDataCacheSize = serverProvider.MemoryDataCacheSize = MemorySize;

            //Set application transaction size on destination provider.
            serverProvider.ApplicationTransactionSize = BatchSize;

            //Count transactions
            serverProvider.ChangesApplied += 
                new EventHandler<DbChangesAppliedEventArgs>(RemoteProvider_ChangesApplied);

            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = _pDirection;

            // Create SyncOperations Statistics Object

            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
            _Statics.Add(new _CSyncDetails
            {
                UploadChangesTotal = syncStats.UploadChangesTotal,
                SyncStartTime = syncStats.SyncStartTime,
                DownloadChangesTotal = syncStats.DownloadChangesTotal,
                SyncEndTime = syncStats.SyncEndTime
            });

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();
            return _Statics;
        }

        private static void RemoteProvider_ChangesApplied(object sender, DbChangesAppliedEventArgs e)
        {
            Console.WriteLine("Changes Applied");
        }

        public void _MSync()
        {
            // Define the Products table.
            List<string> _Tablelist = new List<string>();
            _Tablelist.AddRange(_cSynchronization._MGetAllTableList());

            foreach (string tbl in _Tablelist)
            {
                SetUp(tbl);
                _cSynchronization.Synchronize(tbl + "_SCOP", SyncDirectionOrder.DownloadAndUpload);
            }
        }
    }
}

Select Statements for Before and After Tests

SELECT * 
FROM SyncTestServer.dbo.Table1

SELECT * 
FROM SyncTestServer.dbo.Table2

SELECT * 
FROM SyncTestClient.dbo.Table1

SELECT * 
FROM SyncTestClient.dbo.Table2

Before the Sync

This was the DB state before the sync.

enter image description here

After Sync

This was the state afterward. So, the sync appears to have worked.

enter image description here

Reattempted with a Remove Server and Three Concurrent Syncs

This is the connection string for the remote DB.

public const string ServerConnString =
    @"data source=x0x0x0x0x0x.database.windows.net,1433;initial catalog=SyncTestServer01;user id=xoxoxox@xoxoxox;password=xoxoxoxoxox;MultipleActiveResultSets=True;";

This is the modification to mimic three concurrent synchronizations.

public class Program
{
    public static string[] ClientConnString = new string[] 
        {
            @"Data Source=.\SQLExpress;initial catalog=SyncTestClient01;integrated security=True;MultipleActiveResultSets=True;"
            ,@"Data Source=.\SQLExpress;initial catalog=SyncTestClient02;integrated security=True;MultipleActiveResultSets=True;"
            ,@"Data Source=.\SQLExpress;initial catalog=SyncTestClient03;integrated security=True;MultipleActiveResultSets=True;"
        };

    static void Main(string[] args)
    {
        foreach (var connString in ClientConnString)
        {
            Action action = () =>
            {
                _cSynchronization sync = new _cSynchronization();
                sync._MSync(connString);
            };
            Task.Factory.StartNew(action);
        }

        Console.ReadLine();
    }
}

I'm afraid I wasn't able to recreate the error that you experienced. Please turn on tracing and then post the tracing results to your questions. That way we can analyze the trace and see what's problematic.

Upvotes: 6

Related Questions