如何从 CDC 中删除列(在 SQL Server 中) [英] How to remove columns from CDC (in SQL Server)

问题描述

我使用下面的代码在表上启用了 CDC，默认情况下它包括所有列.还有另一种方法可以在指定列的同时在表上启用 CDC(代码如下).但是，对我来说，为时已晚 - 鉴于我的 CDC 已经创建并包含所有列.如何从 CDC 观察列表中删除我不想要的列(我在元数据中到处搜索，但找不到任何内容):

I've enabled CDC on a table, using the code below, and by default it includes all of the columns. There is another way to enable CDC on a table while SPECIFYING the columns (code is given below). However, for me, that's too late - given my CDC was already created and includes ALL of the columns. How do I remove the columns I don't want from the CDC watch list (I searched everywhere in the meta data and couldn't find anything):

-- The following enabled the CDC on a table:
EXECUTE sys.sp_cdc_enable_table 
            @source_schema = N'dbo',
            @source_name = N'TableName',
            @role_name = N'cdc_Admin',  
            @supports_net_changes = 1

-- Now all of the columns are included in the CDC. 

--Alternate (which is too late now given my CDC on table was already created/performed)
EXECUTE sys.sp_cdc_enable_table      
@source_schema = N'dbo'    , 
@source_name = N'Orders'    , 
@role_name = N'cdc_Admin'    , 
@captured_column_list = N'OrderID,CustomerAccount,Product,SalesPerson'

推荐答案

CDC 允许您同时为给定的表提供最多两个活动的捕获实例(以适应基础表上的架构更改).因此，为了迁移到您指定列的新捕获实例(正如您在第二次调用 sys.sp_cdc_enable_table 时所做的那样)，请为 @capture_instance 提供一个值> sys.sp_cdc_enable_table 中的参数并迁移您的 ETL 作业以使用它.然后，一旦您确定没有任何东西在使用旧的捕获实例，请再次使用 sys.sp_cdc_disable_table 删除它，为 @capture_instance 提供一个值(这一次，旧的).

CDC allows you to have up to two capture instances active for a given table at the same time (in order to accommodate schema changes on the underlying table). So, in order to migrate to a new capture instance where you specify the columns (as you do in your second call to sys.sp_cdc_enable_table), provide a value for the @capture_instance parameter in sys.sp_cdc_enable_table and migrate your ETL jobs to use it. Then, once you're sure that nothing is using the old capture instance, drop it using sys.sp_cdc_disable_table again providing a value for @capture_instance (this time, the old one).

这篇关于如何从 CDC 中删除列(在 SQL Server 中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！