none
WCF sync using SqlSyncProvider RRS feed

  • Question

  • Hi All,

    Based on the article http://207.46.16.248/en-us/library/cc807255%28SQL.100%29.aspx I was trying to implement the library to sync the database over the WCF. However I used the SqlSyncProvider instead of DbSyncProvider.

    However when I try to run the sync process in the BeginSession method of SqlSyncProvider I am receiving an error "The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.".

    Please advise is it possible to use SqlSyncProvider directly to sync over the WCF? I am using this since I already have sync related object in the system that are being used by the internal sync routine (withing LAN). I need to write another application that enables the sync over the internet and need to write WCF for that.

    Getting very frustrated with this error.
    Monday, February 21, 2011 3:37 PM

Answers

  • I BeginSession method of my WCF service I need to call provider's BeginSession method to setup the adapters!

    ex.

    void ISyncContract.BeginSession(string scopeName, SyncIdFormatGroup idformatGroup)
    {
           string localConnection = "MYCONNECTIONSTRING";
           peerProvider = new SqlSyncProvider(scopeName, new System.Data.SqlClient.SqlConnection(localConnection),string.Empty,"sync");           
           peerProvider.BeginSession(SyncProviderPosition.Remote,new SyncSessionContext(idformatGroup, null));
    }

    • Marked as answer by Varde Tuesday, February 22, 2011 9:29 AM
    Tuesday, February 22, 2011 9:29 AM

All replies

  • I believe I have configured something wrong and getting trapped in following method of sync framework:

     

    For any reason the framework is unable to find schema_info table and setting the IsProvisioned property to False.

    protected override SyncSchemaInfo ReadSyncSchemaInfo(IDbConnection connection, IDbTransaction transaction, string objectPrefix, string objectSchema)
    {
        IDataReader reader = null;
        IDbCommand command = null;
        SyncSchemaInfo info;
        bool flag = false;
        bool flag2 = false;
        try
        {
            flag = SyncUtil.OpenConnection(connection);
            if (flag && (transaction != null))
            {
                throw new DbSyncException(SyncResource.GetString("OpenedConnectionWithNonNullTransaction"));
            }
            if (transaction == null)
            {
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                flag2 = true;
            }
            if (this.SelectSchemaInfoCommand == null)
            {
                SyncTracer.Error("Missing command '{0}' ", new object[] { "SelectSchemaInfoCommand" });
                throw SyncExptEx.MissingProviderCommand("SelectSchemaInfoCommand", base.GetType().ToString(), "http://msdn.microsoft.com/sync");
            }
            string quotedTableName = ManagementUtils.GetQuotedPrefixedName(objectPrefix, ManagementUtils.SchemaInfoTableDefaultName, objectSchema);
            string str2 = ManagementUtils.GetQuotedPrefixedName(objectPrefix, ManagementUtils.ScopeTableDefaultName, objectSchema);
            if (!SqlManagementUtils.TableExists(connection, transaction, quotedTableName))
            {
                if (SqlManagementUtils.TableExists(connection, transaction, str2))
                {
                    if (SqlManagementUtils.ColumnsForTable((SqlConnection) connection, (SqlTransaction) transaction, str2).Contains("[" + SqlSyncScopeTableHelper.ScopeNameColDefaultName + "]"))
                    {
                        SyncTracer.Error(SyncResource.GetString("SchemaInfoTableMissing"));
                        throw new DbProvisioningException(SyncResource.GetString("SchemaInfoTableMissing"));
                    }
                    return SqlSyncStoreMetadataUpgrade.V2_0Version;
                }
                SyncSchemaInfo currentVersion = SqlSyncStoreMetadataUpgrade.CurrentVersion;
                currentVersion.IsProvisioned = false;
                return currentVersion;
            }
            command = connection.CreateCommand();
            command.CommandText = this.SelectSchemaInfoCommand.CommandText;
            command.Transaction = transaction;
            SyncTracer.Verbose("Reading Schema Version Info");
            SyncTracer.TraceCommandAndParameters(command);
            reader = command.ExecuteReader();
            int ordinal = reader.GetOrdinal("schema_major_version");
            int i = reader.GetOrdinal("schema_minor_version");
            int num3 = reader.GetOrdinal("schema_extended_info");
            if (!reader.Read())
            {
                throw new DbVersionException(SyncResource.FormatString("SchemaInfoNotFound", new object[] { ManagementUtils.GetQuotedPrefixedName(objectPrefix, ManagementUtils.SchemaInfoTableDefaultName, objectSchema) }));
            }
            return new SyncSchemaInfo(reader.GetInt32(ordinal), reader.GetInt32(i), reader.GetString(num3));
        }
        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            if (command != null)
            {
                command.Dispose();
            }
            if (this.SelectSchemaInfoCommand != null)
            {
                this.SelectSchemaInfoCommand.Dispose();
            }
            if (flag2)
            {
                transaction.Commit();
                transaction.Dispose();
            }
            if (flag)
            {
                connection.Close();
            }
        }
        return info;
    }


    Monday, February 21, 2011 4:07 PM
  • I got it fixed by explicitly passing objectSchema to my schema name! However now getting traped in GetChangeBatch method of SqlSyncProvider. Getting error "The provider does not have the required command 'SelectScopeInfoCommand' specified.". I already have a application that sync the same database using SqlSyncProvider without setting up and commands for it. Something weird using it within WCF. Can anybody please advise?
    Monday, February 21, 2011 4:22 PM
  • Can you take a look at this sample which demonstrates the Database Sync - SQL Server and SQL Express N-Tier and see if it helps.
    This posting is provided AS IS with no warranties, and confers no rights
    Monday, February 21, 2011 8:33 PM
  • Here is the details of what I found during digging in:

    I am building up WCF component to sync my SQL Express replicas that do not have direct connection to the data server. However I am trapping in "GetChangeBatch" method of SqlSyncProvider with error message "The provider does not have the required command 'SelectScopeInfoCommand' specified.". During digging into the BeginSession method of sync framework I found that it calls "Configure" method internally and sets all the adapters for the scope and set the "_adaptersInitialized" data member to true.

    However when I see the provider after calling BeginSession it shows the adapter count = 0 and "adaptersInitialized" is set to False. Please advise what could be the wrong. I have valid sync schema generated in the databases also passed valid sync schema name(ex. sync) while configuring the provider.

    Please help.

     

    • Marked as answer by Varde Tuesday, February 22, 2011 9:29 AM
    • Unmarked as answer by Varde Tuesday, February 22, 2011 9:29 AM
    Tuesday, February 22, 2011 8:42 AM
  • I BeginSession method of my WCF service I need to call provider's BeginSession method to setup the adapters!

    ex.

    void ISyncContract.BeginSession(string scopeName, SyncIdFormatGroup idformatGroup)
    {
           string localConnection = "MYCONNECTIONSTRING";
           peerProvider = new SqlSyncProvider(scopeName, new System.Data.SqlClient.SqlConnection(localConnection),string.Empty,"sync");           
           peerProvider.BeginSession(SyncProviderPosition.Remote,new SyncSessionContext(idformatGroup, null));
    }

    • Marked as answer by Varde Tuesday, February 22, 2011 9:29 AM
    Tuesday, February 22, 2011 9:29 AM
  • Hi Varde, I am confused... Did you succeed in your effort of using SqlSyncProvider in the WCF service? I went through the MS article in your posting, but I see that in your code there are differences in your code. So I have questions. 1. Did you use SyncProxy : KnowledgeSyncProvider  on the client side. If so, did you make any changes to the code, compared to the sample in the article? 2. Your implementation of   ISyncContract.BeginSession(string scopeName, SyncIdFormatGroup idformatGroup)  in the WCF service has different signature than the one posted in the article  void BeginSession(string scopeName)  . Could you please elaborate on this?

    Your help is much appreciated!


    Mixer
    Monday, January 16, 2012 10:21 PM