none
Sync Sql2008 R2 DB with a Local CE Database using a Filtered Scope Template thows a DbNotProvisionedException The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configurati RRS feed

  • Question

  • I added the Schema “Sync” to the Server Database and set the owner to dbo

    I have set up a Filtered Template and applied it to the SQL2008 R2 Server works fine.

    After the code below is run it appears to set up the Server Correctly

     // Step 1 for Parameter-based Filter
                // Create a scope named "filtered_candidate_template", and add two tables to the scope.
                // GetDescriptionForTable gets the schema of each table, so that tracking 
                // tables and triggers can be created for that table.
                var scopeDesc = new DbSyncScopeDescription("filtered_candidate_template");
     
                // Set a description of the template.
                scopeDesc.UserComment = "Template for Candidate and CandidateForm tables. Candidate data is filtered by DeviceID parameter.";
     
                //// Definition for tables.
                DbSyncTableDescription candidateDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Candidate", _serverConn);
                scopeDesc.Tables.Add(candidateDescription);
                DbSyncTableDescription candidateFormDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("CandidateForm", _serverConn);
                scopeDesc.Tables.Add(candidateFormDescription);
     
                // Create a provisioning object for "filtered_candidate_template" that can be used to create a template
                // from which filtered synchronization scopes can be created. We specify that
                // all synchronization-related objects should be created in a 
                // database schema named "Sync". If you specify a schema, it must already exist in the database.
                var serverTemplate = new SqlSyncScopeProvisioning(_serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                serverTemplate.ObjectSchema = "Sync";
     
                // Specify the column in the Candidate table to use for filtering data, 
                // and the filtering clause to use against the tracking table.
                // "[side]" is an alias for the tracking table.
                // The DeviceID column that defines the filter is set up as a parameter in this template.
                // An actual customer type will be specified when the synchronization scope is created.
                serverTemplate.Tables["Candidate"].AddFilterColumn("DeviceID");
                serverTemplate.Tables["Candidate"].FilterClause = "[side].[DeviceID] = @deviceID";
                var param = new SqlParameter("@deviceID"SqlDbType.UniqueIdentifier);
                serverTemplate.Tables["Candidate"].FilterParameters.Add(param);
     
                // Create the "filtered_candidate_template" template in the database.
                // This action creates tables and stored procedures in the database, so appropriate database permissions are needed.
                //TextWriter tw = new StreamWriter("Forms.txt");
                //tw.WriteLine(serverTemplate.Script());
                //tw.Close();
     
                //string theSQL = serverTemplate.Script();
                serverTemplate.Apply();

     

     

    Now I applit it with the below code

     var serverProvDevice = new SqlSyncScopeProvisioning(_serverConn);
                serverProvDevice.ObjectSchema = "Sync";
                serverProvDevice.PopulateFromTemplate("FilteredCandidates""filtered_candidate_template");
     
                //[To Do get a Device ID from List]  
                // This is hard coded atm
                var theDevice = new Guid("8a80a4db-4d9f-4c23-aa76-8a9d0f7bdefd");
     
                serverProvDevice.Tables["Candidate"].FilterParameters["@deviceID"].Value = theDevice;
                serverProvDevice.UserComment = "Candidate data includes only Candidates for the device.";
                serverProvDevice.Apply();

     

    The tables in the SQL server DB look like this Sync.Candidate_tracking

     

    Again looks like all is there.

     

    I then so the Client CE Database provisioning

     // Now provision the SQL Server Client Side database with the new filtered scope.
                DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("FilteredCandidates"null"Sync", _serverConn);
                var clientSqlCeConfig = new SqlCeSyncScopeProvisioning(_clientConn, clientSqlDesc);
                clientSqlCeConfig.ObjectPrefix = "Sync";
                clientSqlCeConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
                clientSqlCeConfig.Apply();
     
    After I call this code I end up with what looks like a correct schema. I see the table in the CE database named Sync_Candidate_tracking . This is because CE does not support Schemas Does this what The tables look like?
     
    So at Think Point I’m thinking ok working like all the samples / Docs / Help files say.
    Now when I try to Sync with the below code
     // After the filtered scope has been defined and the client database is provisioned, the client can be synchronized by creating SqlSyncProvider objects
               // for the filtered scope in the client and server databases, associating the providers with a SyncOrchestrator object, and by calling the Synchronize method.
              
     
               // create the sync orhcestrator
                var syncOrchestrator = new SyncOrchestrator();
     
            
                //// set local provider of orchestrator to a CE sync provider associated with the 
                //// FilteredCandidates in the SyncCompactDB compact client database
                syncOrchestrator.LocalProvider = new SqlCeSyncProvider("FilteredCandidates", _clientConn, "Sync");
                
                // set the remote provider of orchestrator to a server sync provider associated with
                // the FilteredCandidates in the SyncDB server database
                syncOrchestrator.RemoteProvider = new SqlSyncProvider("FilteredCandidates", _serverConn, null"Sync");
     
                // set the direction of sync session to Upload and Download
                //syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
     
                // execute the synchronization process
                SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
     
     
    The last call to = syncOrchestrator.Synchronize(); throws the execption
    DbNotProvisionedException 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.
     
    I’m trying to debug on VS2010 on Windows 7 64bit. I’m using the 2.1 SDK and I have the x86 and x64 runtimes installed.
     
     private readonly SqlCeConnection _clientConn = new SqlCeConnection(@"Data Source='|DataDirectory|\LocalCeDb.sdf'");
     
    This is the connection string.
     
    Any thoughts would be great.
    Thanks
    Tuesday, November 2, 2010 3:42 PM

Answers

  • You have got to be kidding me. I was creating the CE Database from Within VS2010 as soon As I created a empty BD from SQL Server Management Studio it all works like a champ!

     

    Note to self Do Not use VS2010 to Create a CE database!

     

    That only took about 20 hours of of life to figure out. Hope it saves someone else some time.

    • Marked as answer by INuke Thursday, November 4, 2010 1:47 PM
    Tuesday, November 2, 2010 4:20 PM

All replies

  • You have got to be kidding me. I was creating the CE Database from Within VS2010 as soon As I created a empty BD from SQL Server Management Studio it all works like a champ!

     

    Note to self Do Not use VS2010 to Create a CE database!

     

    That only took about 20 hours of of life to figure out. Hope it saves someone else some time.

    • Marked as answer by INuke Thursday, November 4, 2010 1:47 PM
    Tuesday, November 2, 2010 4:20 PM
  • Hi,

    I wrote an exact same app following your repro steps and sync worked well for me. Can you check if your app that ran SyncOrchestrator.Synchronization has enough permission to access the CE database file, and the SQL Server connection username/password can access the tables in "Sync" schema?

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, November 3, 2010 12:44 AM
    Moderator
  • How did you create your CE Database? When I created the database from within VS2010 I get the error, When I used SSMS To create it it runs fine. I'm on a 64Bit Win 7. Also using SQL2008 R2.
    Wednesday, November 3, 2010 10:18 AM
  • Just don't try to add another Scope in the 'sync' schema. That's still a known issue.

    Hopefully saving you additional pain-point in the near future.

    Wednesday, November 3, 2010 9:06 PM
  • Hi,

    Here is the code to create a CE database:

                string ceConnStr = @"Data Source = 'c:\cetest.sdf'";

                using (SqlCeEngine ceEngine = new SqlCeEngine(ceConnStr))
                {
                    ceEngine.CreateDatabase();
                    ceEngine.Dispose();
                }

    With my code snippet, the app needs to be run with write permission to the root of c: drive.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, November 4, 2010 3:02 AM
    Moderator