CDC question

• SQL_Surfer

  SSCrazy Eights

  Points: 8316

  More actions

  May 14, 2019 at 9:20 pm

  #3642120

   

  I' ve CDC setup like this on my 2014 SQL server to capture the changes made through out the day and we load them in the historical table nightly to report off of. This is the code I got it from this site few years back when I implemented it.

  This piece worked great on SQL 2008 but not sure if something has changed in 2014, it pulls bunch of records that didn't change at all adding garbage records in the historical table. Part of it is which I think is coming from join on Start_Lsn.

  Any help would be much appreciated as it is impacting our production.

   

   

  declare @DateForRun CHAR(8) = NULL

  DECLARE @from_lsn BINARY (10)

  ,@to_lsn BINARY (10)

  ,@Column_ordinal INT;

  IF @DateForRun IS NULL

  SET @DateForRun = CONVERT(VARCHAR, getdate() - 1, 112)

  SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_table');

  SET @to_lsn = sys.fn_cdc_get_max_lsn();

  SET @Column_ordinal = sys.fn_cdc_get_column_ordinal('dbo_table', 'NASTAT');

  SELECT a.DMUSER --User changed who changed it

  ,c.tran_end_time

  ,a.NANUM --PK

  ,'Status' as FieldName

  ,b.NASTAT AS Old_Value

  ,a.NASTAT AS New_Value

  FROM cdc.fn_cdc_get_all_changes_dbo__table(@from_lsn, @to_lsn, 'all') a

  JOIN cdc.dbo__table_CT b ON a.__$start_lsn = b.__$start_lsn

  JOIN cdc.lsn_time_mapping c ON a.__$start_lsn = c.start_lsn

  WHERE a.__$operation = 4

  AND b.__$operation = 3

  AND sys.fn_cdc_is_bit_set(@Column_ordinal, a.__$update_mask) = 1

  AND CONVERT(VARCHAR, c.tran_end_time, 112) = @DateForRun