Feeds:
Posts
Comments

Posts Tagged ‘Change data capture instance has not been enabled for the source table’

Change Data Capture (CDC) has been discussed in detail in my earlier articles. In this article, I will discuss an error message that I came across while disabling the CDC for a table due to insufficient parameters.

error

Let me explain this error in detail :

Message Number: 22960

Severity : 16

Error Message: Change data capture instance ‘%s’ has not been enabled for the source table ‘%s.%s’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Error Generation:

Let me DISABLE CDC feature on a particular table.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = NULL
GO

Msg 22960, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 75
Change data capture instance ‘(null)’ has not been enabled for the source table ‘HumanResources.Department’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Ooopps…… I am unable to disable CDC on this table. How to fix it ?

Resolution:
The resolution is very simple because partially it is explained in the error message itself. Let me fix this error step by step.

Step 1 :
First of all, you need to execute sys.sp_cdc_help_change_data_capture (A system stored procedure) in order to find the capture instance name of that particular table name as shown below.

USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

error message 22960.1

Step 2 :
The next step is to get the capture instance name of the particular table from the above result set and pass it in the sys.sp_cdc_disable_table (A system stored procedure) to disable the CDC feature from that table as shown below.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO

Conclusion :

Remember, whenever you need to disable CDC feature from any table, find out the capture instance name of the table and then disable it using sys.sp_cdc_disable_table in order to avoid this error.

Advertisements

Read Full Post »