August 9, 2012 at 9:02 am
Hi everyone, I am playing around with CDC on SQL 2012 Enterprise and run the following simple code, but CDC doesn't show anything about the DELETE. I thought that it will show all transactions. What am I missing? Thanks for your help.
EXEC sys.sp_cdc_enable_db;
create table test1 (
col1 int primary key,
col2 varchar(5),
col3 varchar(5),
col4 varchar(5));
EXEC sys.sp_cdc_enable_table
@source_schema ='dbo',
@source_name ='test1',
@role_name ='dbo_test1',
@supports_net_changes = 1;
insert into test1 (col1, col2, col3, col4)
values (1, 'a', 'a', 'a'),
(2, 'b', 'b', 'b'),
(3, 'c', 'c', 'c');
update test1
set col2 = 'a1'
where col2 = 'a';
delete from test1
where col2 = 'c';
insert into test1 (col1, col2, col3, col4)
values (4, 'd', 'd', 'd')
--------
select * from cdc.dbo_test1_CT;
DECLARE @begin_time AS DATETIME = GETDATE() - 1;
DECLARE @end_time AS DATETIME = GETDATE();
DECLARE @from_lsn AS BINARY(10)
= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
DECLARE @to_lsn AS BINARY(10)
= sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
DECLARE @min_lsn AS BINARY(10)
= sys.fn_cdc_get_min_lsn('dbo_test1');
IF @from_lsn < @min_lsn SET @from_lsn = @min_lsn;
SELECT * FROM
cdc.fn_cdc_get_net_changes_dbo_test1(@from_lsn, @to_lsn,
N'all');
August 9, 2012 at 2:41 pm
I'm still not sure why the delete doesn't show, but I set @supports_net_changes to 0 now and then used cdc.fn_cdc_get_all_changes_dbo_test1 instead to get all the rows, including the delete. Just in case someone else has the same "issue".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply