CDC

delete from test.shift

--To enable CDC for the database

USE MF

GO

EXEC sys.sp_cdc_enable_db

GO

--to enable the cdc for the particular table and schema

USE MF

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'test',

@source_name   = N'Shift',

@role_name     = NULL

GO

--To enable the Clean up job

--@retention indictaes  minutes

--The maximum value is 52494800 (100 years).

EXECUTE sys.sp_cdc_change_job

    @job_type = N'cleanup',

    @retention = 52494800 ;

GO

--To see the jobs are running

--sp_cdc_help_jobs

create table test.shift

(

shiftid int identity(1,1),

Name varchar(20),

starttime date,

Endtime date,

ModifiedDate date

)

insert into test.shift  values ('Day',GETDATE(),GETDATE(),GETDATE())

insert into test.shift  values ('Evening',GETDATE(),GETDATE(),GETDATE())

insert into test.shift  values ('Night',GETDATE(),GETDATE(),GETDATE())

select * from test.shift

INSERT INTO test.[Shift]

       ([Name],[StartTime],[EndTime],[ModifiedDate])

VALUES ('Tracked Shift',GETDATE(), GETDATE(), GETDATE())

--To see any error and failing jobs

--select * from sys.dm_cdc_errors

EXEC sys.sp_cdc_help_change_data_capture

UPDATE [test].[Shift]

SET starttime = '2012-12-11'

   

WHERE ShiftID = 3

DELETE

FROM [test].[Shift]

WHERE ShiftID = 4

GO

SELECT  *, dbo.Hex2Bin('_$update_mask')

  FROM [MF].[cdc].[test_Shift_CT]

  select dbo.Hex2Bin ('0x06')

Create Procedure dbo.usp_timeDiff 

( 

 @begin_time DATETIME,  

 @end_time DATETIME 

) 

as begin 

 

DECLARE  @begin_lsn BINARY(10), @end_lsn BINARY(10); 

 

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than',@begin_time);  

SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time);  

SELECT *  

FROM cdc.fn_cdc_get_all_changes_test_Shift(@begin_lsn,@end_lsn,'all')  

 

End  

--  to see the changes for the particular period

exec usp_timeDiff '2013-12-12 00:00:00' ,'2013-12-12 14:55'

--In order to effect the changes made in cleanup job ,below will stop

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

GO

--default is capture

EXEC sys.sp_cdc_start_job

--Cleanup is the parameter we need to specify

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