how to read from cdc tables?
I wanted to try out the
change data capturefeature 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
@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
cdcis created in
system tableswith some tables, so I thought let's see what is in table cdc.dbo_tblUser_CT
but then I get this
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
I agree with Learning_DBAdmin, you should always use the https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15#download-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_CTthat 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, https://www.codeproject.com/Articles/1079275/Debug-Stored-Procedures-in-Visual-Studio-and-SSMS nowadays. Please see this https://stackoverflow.com/a/41262963/5059085 on how to set it up.