Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_

Seemingly valid code for querying the latest tracked changes in the table Fields:

DECLARE @Begin_LSN BINARY(10), @End_LSN BINARY(10)
SET @Begin_LSN = sys.fn_cdc_get_min_lsn('Fields')
SET @End_LSN = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_ordering_Fields (@Begin_LSN, @End_LSN, N'all')
GO

generates the following error message:

Msg 313, Level 16, State 3, Line 5
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .

However, if I check for actual values they all seem to be valid (not null), the query

SELECT @Begin_LSN, @End_LSN, N'all';

returns

0x00000000000000000000  0x00002594000002130001  all
like image 656
Alexander Galkin Avatar asked Apr 30 '13 12:04

Alexander Galkin


3 Answers

This error message is somewhat misleading and basically hints that some parameters might be out of bound. The message is not further customized because of the limitation of table functions.

The zero value (0x00000000000000000000) is not a valid one. The sys.fn_cdc_get_min_lsn() returns this value if it cannot find the appropriate capture instance name. This name might deviate from the actual table name. See this question for more details

See the following question for more details.

like image 111
Alexander Galkin Avatar answered Nov 13 '22 20:11

Alexander Galkin


In my case this error was because of multiple time Enable/Disable CDC at table level and created two capture instance for same table. I fixed this by disabling CDC at database level and enabled again, then it works fine.

like image 44
Tej kumar Avatar answered Nov 13 '22 19:11

Tej kumar


The above answers are correct, but I figured I would add what it was in my case. I was saving the last processed LSN for future runs. But this was a dev database and it was restored from prod. The CDC scripts were re-applied, but I had lost my history. What I needed to do was just remove that last processed LSN so it would go back to getting the minimum LSN for the capture instance and that put me back on the right track. Unfortunately this is not an extremely useful error message.

like image 1
LeftOnTheMoon Avatar answered Nov 13 '22 19:11

LeftOnTheMoon