October 4, 2011 at 10:38 pm
Hi,
I have a table that is CDC enabled already.
I later added a column "LastChangedTime" with DEFAULT getdate().
Now, when I try to drop this default constraint (in order to drop the column as a following step), I am not allowed, with SQL Server saying :
Msg 22979, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 130
The unique index 'PK__USER_LIST__6932806F' on table '[dbo].[USER_LIST]' is used by Change Data Capture. The constraint using this index cannot be dropped or disabled.
Is there a work around to this problem?
I don't have an option to disable and re-enable CDC as I have other columns that have been added later and should not be CDC'd, and other complications.
October 5, 2011 at 1:04 am
Ok,
I have further found out that the unique constraint being mentioned in the error above, no longer exists (has been renamed to PK_USER_LIST which is the existing PK on the USER_LIST table).
But if I execute the system proc EXEC sys.sp_cdc_help_change_data_capture , I see the index_name = PK__USER_LIST__6932806F ,
which perhaps is being checked by sp_cdc_ddl_event_internal and the error being thrown thus.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply