none
SqlException, Invalid Column Name. RRS feed

  • Question

  • Hi, i'm trying to use MS Sync Framework and i am successful synchronizing it when i first run it but when i added additional columns on the server database i start to received exceptions.

    Here's what i did after i added additional columns on the server database. I run this script

    DECLARE @sqlDrop VARCHAR(1000)
    SET @sqlDrop = 'IF EXISTS (SELECT * FROM #table# WHERE object_id = OBJECT_ID(''#name#'')) DROP #what# #name#'
    DECLARE @sqlCommand VARCHAR(1000)
    
    DECLARE @id INT
    SET @id = 0
    DECLARE @name SYSNAME
    DECLARE @prev INT
    
    WHILE 1 = 1
       BEGIN
          /* find traces of synchronization */
    
          -- to be sure that id changed
          SET @prev = @id
    
          -- get the next table
          SELECT TOP 1
                 @id = object_id,
                 @name = name
            FROM sys.tables
           WHERE object_id > @id
           ORDER BY object_id
    
          -- confirm that there is next table
          IF @id = @prev
             BREAK
    
          /* remove traces of synchronization */
    
          -- remove table
          SET @sqlCommand = @sqlDrop
          SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
          SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_tracking')
          SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
          EXEC (@sqlCommand)
    
          -- remove triggers
          SET @sqlCommand = @sqlDrop
          SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.triggers')
          SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete_trigger')
          SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TRIGGER')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_delete_trigger', '_insert_trigger')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_insert_trigger', '_update_trigger')
          EXEC (@sqlCommand)
    
          -- remove stored procedures
          SET @sqlCommand = @sqlDrop
          SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.procedures')
          SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete')
          SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'PROCEDURE')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_delete', '_deletemetadata')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_deletemetadata', '_insert')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_insert', '_insertmetadata')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_insertmetadata', '_selectchanges')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_selectchanges', '_selectrow')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_selectrow', '_update')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_update', '_updatemetadata')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_updatemetadata', '_bulkdelete')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_bulkdelete', '_bulkinsert')
          EXEC (@sqlCommand)
          SET @sqlCommand = REPLACE(@sqlCommand, '_bulkinsert', '_bulkupdate')
          EXEC (@sqlCommand)
    
       END
    
    -- remove scope and schema tables
    SET @sqlCommand = @sqlDrop
    SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
    SET @sqlCommand = REPLACE(@sqlCommand, '#name#', 'schema_info')
    SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
    EXEC (@sqlCommand)
    SET @sqlCommand = REPLACE(@sqlCommand, 'schema_info', 'scope_config')
    EXEC (@sqlCommand)
    SET @sqlCommand = REPLACE(@sqlCommand, 'scope_config', 'scope_info')
    EXEC (@sqlCommand)

    Here's my code to provision the database.

    class Program
    {
    	static void Main(string[] args)
    	{
    		AddScope("CUSTOMER");
    	}
    	static void AddScope(string tablename)
    	{
    		SqlConnection con = new SqlConnection(@"Data Source=.\Express;Database=Server;Integrated Security=True;");
    		
    		DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(tablename + "_SCOPE");
    
    		
    		DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tablename, con);
    
    		// add the table description to the sync scope definition
    		scopeDesc.Tables.Add(tableDesc);
    
    	
    		SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(con, scopeDesc);
    
    		// skipping the creation of table since table already exists on server
    		serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    		// start the provisioning process
    		serverProvision.Apply();
    	}
    }

    Exception Received: System.Data.SqlClient.SqlException
    Exception Message: Invalid column Name 'Name of the added column'

    Regards,





    • Edited by Dikong42 Tuesday, September 12, 2017 8:17 AM
    Tuesday, September 12, 2017 8:11 AM