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

• Question

• 

  

  0

  Sign in to vote

  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