sql-serverazureazure-data-factorycdc

Invalid object name 'cdc.fn_cdc_get_net_changes' and 'cdc.fn_cdc_get_all_changes'

I want to capture the net changes in the CDC table through lookup activity in azure data factory in SQL Server.

I followed this tutorial: Incrementally load data from Azure SQL Managed Instance to Azure Storage using change data capture (CDC)

I am able to run both fn_cdc_get_min_lsn and fn_cdc_map_time_to_lsn and got the respective values. But when running

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_CUST');
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or 
equal', GETDATE());
SELECT count(1) changecount FROM 
cdc.fn_cdc_get_net_changes_dbo_CUST(@from_lsn, @to_lsn, 'all')

I am encountering the error.

enter image description here

enter image description here

A database operation failed with the following error: 'Invalid object name 'cdc.fn_cdc_get_net_changes_dbo_CUST'.'Invalid object name 'cdc.fn_cdc_get_net_changes_dbo_CUST'., SqlErrorNumber=208,Class=16,State=1,

Sample CDC table enter image description here

Solution

  • Select the correct database in the linked service as shown below:

    enter image description here

    Otherwise, you may encounter the error shown below:

    A database operation failed with the following error: 'Invalid object name 'cdc.fn_cdc_get_net_changes_dbo_customers'.'Invalid object name 'cdc.fn_cdc_get_net_changes_dbo_customers'., SqlErrorNumber=208,Class=16,State=1,

    Additionally, ensure that you have enabled change data capture on the table before inserting data into it using the script below:

    EXEC sys.sp_cdc_enable_db 

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'CUST', 
@role_name = NULL,
@supports_net_changes = 1

    By following the above steps, you will be able to obtain the output of the lookup activity as shown below:

    enter image description here