I have no solution to this..but I was wondering if you ever came up with a solution. I have read in a few places that you need to make a second capture instance for the table. Then you will have the history in one table and the new history in the new capture instance table that you create. The first capture instance will eventually be cleared out whenever the "garbage collector" deletes the information for the set time you have it to delete information.
The problem with this is that if you have to add another column to the table while the first capture instance still has data in it. How do you retain that information without running into the problem that you ran into with preserving the lsn's?
Also, do you know how to make a second capture instance of the table? I've been struggling with that and have come up with no solutions so far?
Thanks so much and hope this helps out a little
Richard Collette wrote:
Change Data Capture: Preserve capture instance data when adding co
16-Apr-10
When a new column is added to table that is configured for change data
capture (cdc), the capture instance table will not have the new column until
cdc is disabled and re-enabled for the source table. In the process the
existing capture instance is dropped.
I thought I could copy existing data out to a temp table and then copy back
using the following SQL. However, other CDC meta information, such as the
cdc.change_tables.start_lsn, becomes invalid.
How can the capture instance history be preserved, using the same capture
instance name, if at all?
Thanks,
Rich
/*Change Data Capture Test - Alter table definition test */
/*Enter restricted mode so we do not lose data changes during this
process*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC OFF
alter database ChangeDataCaptureTest set RESTRICTED_USER with ROLLBACK
IMMEDIATE
go
/*Add a column to the table*/
alter table dbo.Table1 add value3 varchar(20) DEFAULT '' not null
/*Copy the existing change tracking into a temp table*/
select * into cdc.dbo_Table1_temp from cdc.dbo_Table1_CT
/*Add the new column to the temp table so that we do not have to map
all columns when we copy back, note that we use NULL as the default*/
alter table cdc.dbo_Table1_temp add value3 varchar(20) DEFAULT NULL
/*Disable CDC on the source table, this will drop the associated cdc
table*/
exec sys.sp_cdc_disable_table
@source_schema='dbo',
@source_name='Table1',
@capture_instance='dbo_Table1'
/*Enable CDC for the table which recreates the CDC table*/
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Table1',
@role_name = NULL,
@supports_net_changes = 1,
@filegroup_name = N'ChangeDataCapture'
GO
/*Insert values from the temp table back into the new CDC Table*/
Insert into cdc.dbo_Table1_CT
SELECT *
From cdc.dbo_Table1_temp
go
/*Drop the temp table*/
drop table cdc.dbo_Table1_temp
/*Go back into multi-user mode*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC ON
alter database ChangeDataCaptureTest set MULTI_USER
go
/*Add a new row to the table*/
insert into table1
values(12,'zz','g')
Previous Posts In This Thread:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials/aspnet/6dc05c04-c7f9-40cc-a2da-88...2e6d891
FAQ
Search
Profile
Private Messages
Log in
