SQLServer 2008 Instance My client enabled Change Data Capture in lieu of keeping a history of changes in the baseline table. My challenge is that I need to determine if anything changed since the last time I executed.
I have the following logic:
declare @begin_lsn binary(10), @end_lsn binary(10), @prev_run_dt datetime
select @prev_run_dt = getdate() -1 --- example only--getting start date of last successful run
select @begin_lsn = sys.fn.cdc_map_time_to_lsn('smallest greater than', @prev_run_dt)
select @end_lsn = sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_all_changes_<change_capture>(@begin_lsn,@end_lsn, N'all')
If there are no changes that have the specific lsn values in my table, the fn_cdc_get_all_changes FAILS..badly..error code 208. According to documentation, you should not query CDC tables directly but use the functions.
Any suggestions on how to get around this?
