Vensyd avatar image
Vensyd asked

Change Data Capture in SQL Server 2012

Am trying to get the net changes on CDC enabled table by passing Min and Max dates. But is throwing below error. Msg 313, Level 16, State 3, Line 24 An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ... . My code is as below: DECLARE @CDate DATE = '2013-03-18' --This is the date after the CDC was enabled on the table DECLARE @count INT; DECLARE @lsnStartDatetime DATETIME; DECLARE @lsnEndDateTime DATETIME; DECLARE @begin_time DATETIME , @end_time DATETIME , @from_lsn BINARY(10) , @to_lsn BINARY(10); SELECT @lsnStartDatetime = CAST(CAST(@CDate AS NVARCHAR(10)) + ' 00:00:00' AS DATETIME) SELECT @lsnEndDateTime = CAST(CAST(@CDate AS NVARCHAR(10)) + ' 23:59:59' AS DATETIME) SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @lsnStartDatetime); SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @lsnEndDateTime); if exists (select * from sys.objects where name = 'EmployeeCDCbyDate' and type = 'u') drop table etl.EmployeeCDCbyDate SELECT * FROM cdc.fn_cdc_get_net_changes_employee(@from_lsn, @to_lsn, N'all') Is it the from_lsn and to_lsn getting from sys.fn_cdc_map_time_to_lsn doesnt match with is mapped aginst the cdc table 'employee' Below code works fine; but it gets all net changes from min max lsn's. DECLARE @min_lsn BINARY(10) = sys.fn_cdc_get_min_lsn ('employee') DECLARE @max_lsn BINARY(10) = sys.fn_cdc_get_max_lsn () SELECT * FROM cdc.fn_cdc_get_net_changes_employee(@min_lsn, @max_lsn, 'all') ORDER BY 1 desc What i need is to get min and max lsn of cdc instance for given date and get the net changes for that date. Any clues? **Edit:** This works fine with first table when i enable on bunch of tables. Ex: USE ERP EXEC sys.sp_cdc_disable_db EXEC sys.sp_cdc_enable_db EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Employee', @capture_instance = 'Employee', @supports_net_changes =1, @role_name = NULL EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'StoreListing', @capture_instance = 'StoreListing', @supports_net_changes =1, @role_name = NULL Go This works fine with Employee table. If i change the order in which they are CDC enabled (if i put storelist first and employee next), then it works fine with employee listing.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
If any of the answers below were helpful to you, please show that by clicking on the thumbs up next to the answer. If any of the answers below solved your problem, please indicate that by clicking on the check box.
0 Likes 0 ·

1 Answer

Usman Butt avatar image
Usman Butt answered
I remember kind of a same behaviour shared on Askssc. Please see this post [Problem with CDC when retrieving changed data from period starting from before CDC was enabled][1] [1]:
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Vensyd avatar image Vensyd commented ·
Hello Usman seems the URL is broken
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Oops..I have corrected it ;). Please check now.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.