x

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
more ▼

asked Aug 13, 2012 at 01:11 PM in Default

wouterp gravatar image

wouterp
0 3 3 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

have a read of this blog, it may help answer you're question...

more ▼

answered Aug 14, 2012 at 04:33 PM

swirl80 gravatar image

swirl80
60 1

Thanks for the pointer Chris. I had come across that blog myself, while it helped me pinpoint the problem, it doesnt explain why my colleague doesnt have this problem. Nor does it give a workable solution to use pre-cdc-enabled dates.
Aug 15, 2012 at 08:05 AM wouterp

I can't answer the question about why it works on your colleagues machine (different servicepack/CU perhaps??).

Can you not perhaps find what the minimum startdate is and change anything that precedes that date to be the fromdate instead and pass that through?

Aug 15, 2012 at 09:10 AM swirl80
(comments are locked)
10|1200 characters needed characters left

@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.
more ▼

answered Aug 15, 2012 at 11:59 AM

wouterp gravatar image

wouterp
0 3 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x595
x18

asked: Aug 13, 2012 at 01:11 PM

Seen: 1550 times

Last Updated: Aug 15, 2012 at 11:59 AM