how to read from cdc tables?

All we need is an easy explanation of the problem, so here it is.

I wanted to try out the change data capture feature of sql server, but I cannot get it to work

What I did

-- enable cdc
EXEC sys.sp_cdc_enable_db

-- enable it on a table, for testing
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'tblUser',  
@role_name     = NULL,  
@supports_net_changes = 1

-- check if it got enabled (yes it did)
select t.name, t.is_tracked_by_cdc,
from sys.tables t where name = 'tblUser'

So, now I just changed some value in a row in that table, and next I wanted to see how it looks.
So I noticed that a schema cdc is created in system tables with some tables, so I thought let’s see what is in table cdc.dbo_tblUser_CT

but then I get this

how to read from cdc tables?

So now I am stuck.
What must I do to see the changed data ?

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) – 15.0.4198.2 (X64)
Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0
(Build 17763: ) (Hypervisor)

Compatibility level is 150

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I agree with Learning_DBAdmin, you should always use the latest series of SSMS. That is likely you’re issue here. The 17 series of SSMS is quite old at this point.

Otherwise you can try manually writing out the script SELECT TOP 1000 * FROM cdc.dbo_tblUser_CT that SSMS would’ve generated for you. (Replace * with the actual column names.)

I know you mentioned you still use the 17 series of SSMS for its debugging capabilities, but the longer you hold onto that version the more issues you’ll find yourself running into. Instead, Visual Studio can debug SQL nowadays. Please see this StackOverflow answer on how to set it up.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply