Problem with CDC when retrieving changed data from period starting from before CDC was enabled
I want to retrieve data that has changed over a certain time period, I use the following query: DECLARE @begin_time datetime , @end_time datetime , @from_lsn binary(10) , @to_lsn binary(10) SET @begin_time = '2011-08-13 13:33'; SET @end_time = '2013-07-05 12:00'; SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time); SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS ChangeDTS , * FROM cdc.fn_cdc_get_all_changes_dbo_LWR_CONTRIBUTIES (@from_lsn, @to_LSN, 'all') Problem is, when I set `@begin_time` to a time before CDC was enabled (as is the case in the query above) I get the infamously unhelpful error message `"An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_..."`. When I put `begin_time` after cdc was enabled (eg '2012-08-13 13:33')[btw I use YMD notation for dates] it works fine. So far so good, well not good, but clear. Weirdest is, it works fine for any date, before or after CDC was enabled (even before WWII works fine) on my colleagues machine. Anybody got an idea where the issue might lie? I would like to be able to set the begin time to any time I please (obviously I wont get the changes from before cdc was enabled, but thats not the point). I just dont want any unexpected error messages when I run this script on a client machine. I primarily use Server 2008R2, but I have the same issue in server 2012. Thanks, Wouter
@swirl80, yeah that is the only way to go, far as I can see. Since its all implemented in C#, its not difficult to do so (not that its particularly difficult in sql lol). Nonetheless, I dont like unexplained differences in the execution of identical scripts. For the record, machine set ups are pretty similar, at least where the Server installation is concerned. Although on second thought, my colleagues machine is 32 bits, mine is 64. Can that make a difference? I can hardly imagine it, but its the only difference I can find.