locked
SQL SERVER: Change Data Capture ERROR RRS feed

  • Question

  • Hell all..

    I am learning of how CDC works.. and trying to follow the example but got error when on the last step where we can see data on _CT table.

    error msg "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ …"

    Could anyone help me? where did I do wrong? or did I miss any step?

    Below are my steps.

    Create Database CDCORigin;

    create table dbo.customer

    (

    id int identity not null

    , name varchar(50) not null

    , state varchar(2) not null

    , constraint pk_customer primary key clustered (id)

    )

    exec sys.sp_cdc_enable_table

        @source_schema = 'dbo',

        @source_name = 'customer' ,

        @role_name = 'CDCRole',

        @supports_net_changes = 1

    select name, type, type_desc, is_tracked_by_cdc from sys.tables

    EXEC sys.sp_cdc_add_job @job_type = N'capture';

    EXEC sys.sp_cdc_add_job @job_type = N'cleanup';

    insert customer values ('abc company', 'md')

    insert customer values ('xyz company', 'de')

    insert customer values ('xox company', 'va')

    update customer set state = 'pa' where id = 1

    delete from customer where id = 3

    declare @begin_lsn binary(10), @end_lsn binary(10)

    -- get the first LSN for customer changes

    select @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')

    -- get the last LSN for customer changes

    select @end_lsn = sys.fn_cdc_get_max_lsn()

    select @begin_lsn,  @end_lsn

    -- get net changes; group changes in the range by the pk

    select * from cdc.fn_cdc_get_net_changes_dbo_customer(

     @begin_lsn, @end_lsn, 'all');

    -- get individual changes in the range

    select * from cdc.fn_cdc_get_all_changes_dbo_customer(

     @begin_lsn, @end_lsn, 'all');

    Tuesday, November 3, 2020 10:12 PM