how to read from cdc tables?

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment