Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have Change Data Capture enabled and working fine. The question that I have is how do I query the data to show users what changes occurred.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN).

First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report on all the change data captured today:

DECLARE 
  @s DATE = SYSDATETIME(),                  -- start today at midnight
  @e DATE = DATEADD(DAY, 1, SYSDATETIME()), -- end tomorrow at midnight
  @sl BINARY(10),                           -- start LSN 
  @el BINARY(10);                           -- end LSN

SELECT 
  @sl = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @s),
  @el = sys.fn_cdc_map_time_to_lsn('largest less than',              @e);

SELECT @sl, @el; -- start LSN and end LSN within today

Then you can use those LSN values in the cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions, depending on what you are after (let's say, for a table called dbo.floob):

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_floob(@sl, @el, 'all');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_floob(@sl, @el, 'all');
share|improve this answer
    
But then I want to query this with the Entity Framework. How would I hook on to the DB. –  Mike Diaz Jun 19 '13 at 18:29
    
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.