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

Question

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

Tremolant

Answer 1

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.

Tremolant

This can also happen , when the supplied @Begin_Lsn value is not present in the change table – Gorge

In my case the lsns are valid. still get this error – Gelignite

select * from [cdc].[fn_cdc_get_net_changes_CM_Attachments](null,null,'all') without lsn also fires the same error – Gelignite

Answer 2

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.

Rearmost

if its a test/dev database, drop and restore the db again fresh and run the scripts.. error resolved in my case... – Mani

Answer 3

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.

Superjacent

Answer 4

I had a very similar issue, this turned out to be 2 problems

The first a typo in the first parameter to the function fn_cdc_map_time_to_lsn, which returned a NULL value which is then passed to the table cdc function resulting the insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ message

The typo was 'largest less than or eqaul' and should've been 'largest less than or equal'

This resulted in a NULL value being returned when passing a datetime as a string as the second parameter.
BUT confusingly would work when passing in the function GETDATE() but not the string value returned from GETDATE()

The second I believe is due to the datetime being used for the second parameter being a value before CDC was enabled. This occurred due to disabling and enabling CDC more than once

Checked using the function fn_cdc_map_lsn_to_time to convert the the @from_lsn value

e.g. the below returned 0x000024FA000010580039

SET @begin_time = '2023-12-08 17:00:00';
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);

and using the map lsn to time function

SELECT sys.fn_cdc_map_lsn_to_time (0x000024FA000010580039)

returned '2023-12-08 17:04:34.950'

so when using a datetime before the above time with 'largest less than or equal' resulted in a NULL value e.g. below resulted in NULL return

SET @end_time = '2023-12-08 17:00:00.000';
SELECT sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

Daw

Answer 5

I had a similar problem to yours. My problem is easier than yours but I want to write here for the case of someone receiving an error code like it.

When I called a function which has parameters, I forgot to enter those parameter. In a windowsform dataset I must use something like Select * from func_for_something(@id, @name). But if I write on dataset with Select * from func_for_something() without parameters, in visual studio I had no attemption . However when I triggered this function somewhere in the program I see an error message like yours.

So in conclusion: don't forget to add parameters when you use a function which has parameters. :) Sorry for my insufficent English

Capsicum