UpdateScopeInfoCommand fails to execute -- error says @scope_sunc_knowledge parameter expected and not supplied

• Question

• 

  

  0

  Sign in to vote

  Hi all-

  I am trying to get a peer-to-peer sync app working between 2 SQL Server 2005 databases that I set up and am having a problem I can't seem to get past, which is also making me wonder if I am missing something fundamental to using DBSyncProvider and DBSyncAdapter.

   

  I have set up a simple Winforms app that attempts to synchronize a simple table - Customer from the Change Tracking and Peers sample from:

  http://msdn.microsoft.com/en-us/library/cc807286.aspx

   

  Anyway, I have the table, meta table and scope table on both peers. I have set up the triggers to update the meta table and inserted sample rows in one peer db only (which also populated the meta table via the triggers). I added scope records on both peers with just a scope name (scope_sync_knowledge and scope_tombstone_cleanup_knowledge are null). Then I try to do my synchronization, but I always get the error:

   

  Failed to execute provider command: 'UpdateScopeInfoCommand'; the transaction was rolled back. Ensure that the command syntax is correct.. ---> System.Data.SqlClient.SqlException: The parameterized query '(@scope_sync_knowledge varbinary(max), @scope_tombstone_cleanup_' expects the parameter '@scope_sync_knowledge', which was not supplied.

   

  At first I wondered if something were getting truncated, but I ran everything under the debugger and everything looks good after the command is bound to the adapter. So now I am wondering if the scope_sync_knowledge field in my scope info table, which is initially NULL, is a problem and needs to be initialized somehow before the initial synchronization.I copied the code from the online sample code and I don't see a value for this field being passed along in the parameterized query  anywhere. Can someone tell me what I am doing wrong here?

  I am including the table structures, and relevant (hopefully) code below in case it helps anyone to recognize my error.

   

  Thanks,

  Glenn

   

  Database Tables (exist on both peers):

   

  CREATE TABLE dbo.Customer
  (
      CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID()
      , CustomerName nvarchar(100) NOT NULL
      , SalesPerson nvarchar(100) NOT NULL
      , CustomerType nvarchar(100) NOT NULL
  )

  CREATE TABLE Sync.Customer_Tracking
  (
      CustomerId uniqueidentifier NOT NULL PRIMARY KEY,         
      sync_row_is_tombstone int DEFAULT 0,
      sync_row_timestamp timestamp,
      sync_update_peer_key int DEFAULT 0,
      sync_update_peer_timestamp bigint,       
      sync_create_peer_key int DEFAULT 0,
      sync_create_peer_timestamp bigint,
      last_change_datetime datetime DEFAULT GETDATE()
  )

  CREATE TABLE Sync.ScopeInfo
  (
      scope_id uniqueidentifier DEFAULT NEWID(),    
      scope_name nvarchar(100) NULL,
      scope_sync_knowledge varbinary(max) NULL,
      scope_tombstone_cleanup_knowledge varbinary(max) NULL,
      scope_timestamp timestamp
  )

   

  Code which creates the provider's UpdateScopeInfoCommand command:

   

              // build the Update Scope Info provider command
              sbCommandTemplate = new StringBuilder( "update Sync.ScopeInfo set " );
              sbCommandTemplate.Append( "scope_sync_knowledge = @scope_sync_knowledge" );
              sbCommandTemplate.Append( ", scope_tombstone_cleanup_knowledge = @scope_tombstone_cleanup_knowledge" );
              sbCommandTemplate.Append( " where scope_name = @scope_name" );
              sbCommandTemplate.Append( " and (@" );
              sbCommandTemplate.Append( DbSyncSession.SyncCheckConcurrency );
              sbCommandTemplate.Append( " = 0 or scope_timestamp = @scope_timestamp" );
              sbCommandTemplate.Append( "); set @" );
              sbCommandTemplate.Append( DbSyncSession.SyncRowCount );
              sbCommandTemplate.Append( " = @@rowcount" );
              providerCmd = new SqlCommand( sbCommandTemplate.ToString() );
              providerCmd.CommandType = CommandType.Text;
  
              providerCmd.Parameters.Add( "@scope_sync_knowledge", SqlDbType.VarBinary, 10000 );
              providerCmd.Parameters.Add( "@scope_tombstone_cleanup_knowledge", SqlDbType.VarBinary, 10000 );
              providerCmd.Parameters.Add( "@scope_name", SqlDbType.NVarChar, 100 );
              providerCmd.Parameters.Add( "@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int );
              providerCmd.Parameters.Add( "@scope_timestamp", SqlDbType.BigInt );
              providerCmd.Parameters.Add( "@" + DbSyncSession.SyncRowCount, SqlDbType.Int ).Direction = ParameterDirection.Output;

             provider.UpdateScopeInfoCommand = providerCmd;

   

  I checked out the query in the debugger and it appears to be formatted correctly without any sql syntax error.

   

  Help!  Please let me know if seeing any further code, etc. might help you to figure out my problem.

   

  Saturday, June 5, 2010 1:44 AM