Tuesday, June 9, 2015

How to stay in sync with others, at your level

== Part Three ! ==

For change data capture, Microsoft has a great overview online

However, for my code, I use something like this:

exec sys.sp_cdc_enable_db;

create table table1 (column1 int);
alter table table1 add nowval datetime;

exec sys.sp_cdc_enable_table n'dbo', n'table1', null, null,null,null,'nowval',n'primary',1;
insert table1 (nowval) values 

First, I have to enable Change Data Capture (CDC) on the database.

Then, for tracking purposes, I'm going to go ahead and make a test target named "table1".

Once I have enabled CDC on the database, and my target, then I enable the target table to track the changes to the column "nowval" using the function sys.sp_cdc_enable_table.

To track and prove the changes, I've included the insert statement I use.

Lastly, I want to see my changes, so I run this query:

SELECT CT.__$start_lsn, CT.__$operation, CT.*, LSN.tran_begin_time, LSN.tran_end_time, LSN.tran_id
FROM CDC.fn_cdc_get_all_changes_DBO_TABLE1(@from_lsn, @to_lsn, N'all') AS CT
INNER JOIN cdc.lsn_time_mapping AS LSN ON CT.__$start_lsn = LSN.start_lsn

No comments:

Post a Comment