Is there bug with cdc.fn_cdc_get_net_changes_.... in SQL Server 2012 - by VenSree

Status : 

 


5
0
Sign in
to vote
ID 782085 Comments
Status Active Workarounds
Type Bug Repros 5
Opened 3/25/2013 4:24:20 PM
Access Restriction Public

Description

Below script to bring net changes from cdc enabled table for given dates.  This always fails on the second table.

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);

select  @begin_time = min(tran_begin_time) from [cdc].[lsn_time_mapping];
Select @end_time = max(tran_begin_time) from [cdc].[lsn_time_mapping];

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

SELECT * FROM cdc.fn_cdc_get_net_changes_CustomerChanges(@begin_lsn, @end_lsn, 'all')

SELECT * FROM cdc.fn_cdc_get_net_changes_ProductChanges(@begin_lsn, @end_lsn, 'all');
Sign in to post a comment.
Posted by JustKevin on 6/18/2015 at 8:56 AM
We are using 2012 SP2.

To save time/effort, we decided to generate the wrapper functions for each of our tables (using sp_cdc_generate_wrapper_function).

Well, since the "dbo.fn_net_changes_" wrapper functions call the "cdc.fn_cdc_get_net_changes_" functions, I can't use the wrapper functions either.

Obviously I can use LSNs instead of time, but it will take longer to roll my own functions/code.

Sorry to hear this is also broke in 2014.



Posted by rrozema on 6/9/2015 at 7:11 AM
Instead of reading the cdc.lsn_time_mapping tables, use the provided sys functions, sys.fn_cdc_get_min_lsn() and sys.fn_cdc_get_max_lsn(). max_lsn() is parameter less, the max_lsn is the same for all tables. The min_lsn however is specific for each table. Hence the fn_cdc_get_min_lsn( '<capture_instance>') needs to be provided the name of the capture instance for each table.


My suggestion is to change your methodolgy: use an lsn-based range, instead of a time based range (i.e. store with your client the last synced lsn, not the last sync time). But in any case you need to check that the begin_lsn is never before the min_lsn for the specific capture instance. Because if it is before min_lsn, you'll get an error saying the parameters specified to fn_cdc_get_net_changes are incorrect.

If you really need to use a time range, please try something like the following:

DECLARE
@begin_time datetime,
@end_time datetime,
@begin_lsn binary(10),
@end_lsn binary(10),
@min_lsn binary(10);

select
@end_lsn = sys.fn_cdc_get_max_lsn(),
@begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);

select @min_lsn = sys.fn_cdc_get_min_lsn('CustomerChanges');

if @min_lsn = 0x00
raiserror( 'No change information for %s is available. Please perform a full load.', 16, 0, 'CustomerChanges');

if not @begin_lsn >= @min_lsn
raiserror( 'Change information for table %s has been purged, incremental load is no longer possible. Please resync this client.', 16, 0, 'CustomerChanges');

SELECT * FROM cdc.fn_cdc_get_net_changes_CustomerChanges(@begin_lsn, @end_lsn, 'all')



select @min_lsn = sys.fn_cdc_get_min_lsn('ProductChanges');

if @min_lsn = 0x00
raiserror( 'No change information for %s is available. Please perform a full load.', 16, 0, 'ProductChanges');

if not @begin_lsn >= @min_lsn
raiserror( 'Change information for table %s has been purged, incremental load is no longer possible. Please resync this client.', 16, 0, 'ProductChanges');

SELECT * FROM cdc.fn_cdc_get_net_changes_ProductChanges(@begin_lsn, @end_lsn, 'all');


-- This is the time you need to store at the client to use for it's next synchronisation.
select @end_time = sys.fn_cdc_map_lsn_to_time(@end_lsn);

Posted by talinkio on 4/13/2015 at 1:22 PM
Same issue in 2014 for me too. Please fix.
Posted by Microsoft on 4/8/2013 at 1:33 PM
Hi VenSree,

given the load here and the backlog of MS Connect items we are working on it will take us some time to get back to you. If this is related to an urgent issue in production I recommend you contact our Support engineers. You may already have a local support representative. If not you'll find more information on http://www.microsoft.com/microsoftservices/en/us/support.aspx.
Anyways, we will get back to you on this one.

Best regards
Jean-Yves Devant
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Posted by VenSree on 4/3/2013 at 10:08 PM
Hello Jean-Yves
Any update on this please?
Posted by Microsoft on 3/29/2013 at 11:16 AM
Hi VenSree,
    
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 

Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server