Monthly Archives: July 2016

USE MADWORKS
 GO 
 --THIS ENABLES CDC ON THE DATABASE 
 sys.sp_cdc_enable_db 
 
 GO 
 -- CREATING A SCHEMA AND TABLE TO BE USED IN CDC 
 
 CREATE SCHEMA example AUTHORIZATION dbo
GO 
 
CREATE TABLE example.cdcsourcetable ( 
sourceid TINYINT PRIMARY KEY CLUSTERED, 
name VARCHAR(256), 
createddatetime DATETIME, 
Updatedatetime DATETIME DEFAULT GETDATE() NOT NULL ) 
 
GO 
SELECT * FROM example.cdcsourcetable 
GO 
-- INSERTING SOME DATA INTO THE TABLE 
INSERT INTO example.cdcsourcetable (sourceid,name,createddatetime) 
SELECT 2, 'Jayanth Kurup ', GETDATE()-1 
GO -- ENABLE CDC ON THE SOURCE TABLE CREATED ABOVE 
sys.sp_cdc_enable_table @source_schema = 'example', --- NAME OF THE SCHEMA TO WHICH THE SOURCE TABLE BELONGS 
@source_name = 'cdcsourcetable' ,-- NAME OF THE SOURCE TABLE WHICH NEEDS TO BE MONITORED 
@role_name = 'cdcadmin', -- A ROLE ASSIGNED TO CDC TO PERFORM ITS OPERATIONS 
@capture_instance = 'cdc_example_Sourcetable' , -- A NAME ASSIGNED TO THE CDC THAT IS BEING CREATED, THIS IS USED TO NAME THE FUNCTIONS WHICH WILL BE CREATED 
---TO ALLOW YOU TO QUERY THE CDC DATA 
@supports_net_changes = 1, -- IF 1 THEN DISPLAY OPTION TO GET SUM OF ALL CHANGES , IF 0 SHOWS A LIST OF ALL CHANGES 
@index_name = 'PK_cdcsourcetable', -- THE PRIMARY KEY INDEX OF THE TABLE , USED TO IDENTIFY UNIQUE ROWS IN THE TABLE I.E ROW VERSIONING. 
@captured_column_list = 'sourceid,name,createddatetime' -- COMMA DELIMITED SET OF COLUMNS WITHIN THE TABLE THAT NEED TO BE MONITORED 
GO -- INSERT THE CHANGE DATA 
INSERT INTO example.cdcsourcetable (sourceid,name,createddatetime) 
SELECT 2, 'Jayanth Kurup ', GETDATE()-1 
GO -- QUERY CDC FOR CHANGES /*SINCE CDC WORKS BY QUERYING THE TRANSCATION LOG FOR CHANGES AND DML THE LSN IS USED TO KEEP TRACK OF THE ORDER FOR THE CHANGES. THE FUNCTION fn_cdc_map_time_to_lsn ALLOWS YOU TO TRACK AN LSN AS PART OF THE TIMESTAMP WHEN THE TRANSACTION OCCURRED. THIS IS USED EVEN WHEN PERFORMING A POINT IN TIME RECOVERY. THE FUNCTION cdc.fn_cdc_get_all_changes_cdc_example_cdcsourcetable IS USED TO QUERY THE CDC AND IDENTIFY THE CHANGED RECORDS.*/ 
DECLARE @from_lsn BINARY (10) 
DECLARE @to_lsn BINARY (10) 
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE()-1); 
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE()); 
-- Return the changes occurring within the query window. 
SELECT * ,'Operation' = CASE WHEN __$operation =1 
			THEN 'DELETE' WHEN __$operation = 2 
			THEN 'INSERT' WHEN __$operation =3 
			THEN 'UPDATE BEFORE IMAGE' WHEN __$operation = 4 
			THEN 'UPDATE AFTER IMAGE' 
			ELSE 'ERROR' END 
			FROM cdc.fn_cdc_get_all_changes_cdc_example_cdcsourcetable(@from_lsn, @to_lsn, 'all'); GO --RESULT SET