How to populate CDC preview source if a table does not have column of datetime datatype and CDC is enabled to the table

Question

How to populate CDC preview source if a table does not have column of datetime datatype and CDC is enabled to the table

Nidhi S 5

Hello, I'm trying to create a CDC preview pipeline in Azure Data Factory.

I have a requirement to capture change data of multiple tables of a particular SQL server.

Here in these multiple tables some have columns with datetime datatype, and some do not.

CDC has been enabled to all the tables irrespective of whether tables have columns with datetime datatype.

My blocker here is CDC preview source option is only populating the tables with datetime datatype columns, but not others even though for which CDC is enabled.

I want the CDC preview to populate all tables after enabling CDC to the tables, is there any settings or how to achieve this.

Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-03T19:57:41.58+00:00
Hello Nidhi S,

Welcome to the Microsoft Q&A forum.

it seems like you didn't enable CDC on the table level.

on your query, can you please add is_tracked_by_cdc and check the value of the column? it is supposed to be '1'

query:

select is_tracked_by_cdc, * from

sys.tables as t

join sys.schemas as s on t.schema_id =s.schema_id

left join cdc.change_tables as c on t.object_id =c.source_object_id

Once you enable CDC on table level, you will see Incremental Colum details as "SQL server CDC"

Please see this screenshot for your reference.

Steps:

Enable CDC on DB level: exec sys.sp_cdc_enable_db CDC on table: EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'tab2', @role_name = NULL, @filegroup_name = NULL, @supports_net_changes = 1 GO

I hope this helps. Please let me know if you have any further questions.
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-03T20:56:00.7266667+00:00

I was able to reproduce the issue when there is no CDC enabled on the DB level.

Please see the screenshot for your reference.
Franklin John 25 Reputation points

2023-08-04T03:35:55.3+00:00

@BhargavaGunnam-MSFT Thanks for the reply, We already enabled CDC in 2 database tables. It works in the first database

When we are connecting to another database. It's not showing in the but CDC is already enabled in Azure SQL. The reference image is below.

It's a very critical issue please help as to resolve this. Till last month Incremental column was not available
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-08T20:03:28.1266667+00:00

Hello Franklin John and Nidhi S,

I am checking to see if you were able to resolve the issue with the above suggestion.

1 answer

Your answer

Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-03T19:57:41.58+00:00

Hello Nidhi S,

Welcome to the Microsoft Q&A forum.

it seems like you didn't enable CDC on the table level.

on your query, can you please add is_tracked_by_cdc and check the value of the column? it is supposed to be '1'

query:

select is_tracked_by_cdc, * from

sys.tables as t

join sys.schemas as s on t.schema_id =s.schema_id

left join cdc.change_tables as c on t.object_id =c.source_object_id

Once you enable CDC on table level, you will see Incremental Colum details as "SQL server CDC"

Please see this screenshot for your reference.

Steps:

Enable CDC on DB level: exec sys.sp_cdc_enable_db CDC on table: EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'tab2', @role_name = NULL, @filegroup_name = NULL, @supports_net_changes = 1 GO

I hope this helps. Please let me know if you have any further questions.
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-03T20:56:00.7266667+00:00

I was able to reproduce the issue when there is no CDC enabled on the DB level.

Please see the screenshot for your reference.
Franklin John 25 Reputation points

2023-08-04T03:35:55.3+00:00

@BhargavaGunnam-MSFT Thanks for the reply, We already enabled CDC in 2 database tables. It works in the first database

When we are connecting to another database. It's not showing in the but CDC is already enabled in Azure SQL. The reference image is below.

It's a very critical issue please help as to resolve this. Till last month Incremental column was not available
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-08-08T20:03:28.1266667+00:00

Hello Franklin John and Nidhi S,

I am checking to see if you were able to resolve the issue with the above suggestion.

Answer 1

Bhargava-MSFT 31,246 Microsoft Employee

Hello Franklin John

Since you have already enabled CDC on the DB and table level, please confirm if you have enabled @supports_net_changes =1 on the table level.

I believe this is causing the issue here.

I see the same behavior when suppots_net_changes is not enabled on the tables.

EXEC sys.sp_cdc_enable_table  
   @source_schema  = N'dbo',  
   @source_name    = N'tab2',  
   @role_name      = NULL,  
   @filegroup_name = NULL,  
   @supports_net_changes = 1
GO

Share via

How to populate CDC preview source if a table does not have column of datetime datatype and CDC is enabled to the table

1 answer

Your answer