-1

I have a table in a SQL Server 2019 instance which is being CDC tracked. However when I want to rename a column it failes with the error:

Cannot alter column 'Columnn2' because it is 'REPLICATED.

I can work around the problem with the following steps.

  • Disable CDC on table
  • Change column name
  • Enable CDC on table Is there a better way to solve this problem? Script to reproduce:
    CREATE DATABASE cdc_rename_error;
    GO
    
    USE cdc_rename_error;
    GO
    
    /*enable on database level*/
    EXEC sys.sp_cdc_enable_db;
    GO
    
    /*create table*/
    CREATE TABLE dbo.NewTable
    (
        ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
        Column1 VARCHAR(50) NOT NULL,
        Columnn2 VARCHAR(25) NULL
    );
    GO
    
    /*enable cdc*/
    EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
                                 @source_name = N'NewTable',
                                 @role_name = NULL;
    
    
    /*try to rename table, which gives an error*/
    EXEC sp_rename N'[dbo].[NewTable].[Columnn2]', N'Columnn2', N'COLUMN';
    GO
    
    /*cleanup*/
    USE master;
    GO
    
    DROP DATABASE cdc_rename_error;
    GO
0

1 Answer 1

1

Is there a better way to solve this problem?

No, at least not a simpler way, this is how CDC was implemented by design as discussed in What is change data capture (CDC)? - Handling changes to source table:

To accommodate column changes in the source tables that are being tracked is a difficult issue for downstream consumers. Although enabling change data capture on a source table does not prevent such DDL changes from occurring, change data capture helps to mitigate the effect on consumers by allowing the delivered result sets that are returned through the API to remain unchanged even as the column structure of the underlying source table changes...

To accommodate a fixed column structure change table, the capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture...

Typically, the current capture instance will continue to retain its shape when DDL changes are applied to its associated source table.

I believe your current process of disabling CDC, making the change, and re-enabling CDC on that Table is the correct process to follow.

Note by disabling and re-enabling CDC you will lose the previous history you had on that Table. It is usually recommend to save the history to a temp table and re-insert it back into the new CDC table after re-enabling. See Erik Darling's GitHub on CDC for code examples on how to handle such kind of cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.