how to read from cdc tables?



  • 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

    enter image description here

    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_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, 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2