Cannot install Data Collection for CDC using code from Microsoft Learn Error: XML Validation: Declaration not found for element 'TSQLQueryCollector'. Location: /*:TSQLQueryCollector[1]

Question

I am follow instructions from Microsoft Learn to set up CDC Data Collection in the MDW on a SQL Server 2019 instance. From https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/administer-and-monitor-change-data-capture-sql-server?view=sql-server-ver15
So when I execute the following code from the website:

DECLARE @parameters xml;  
DECLARE @collection_item_id int;  
SELECT @parameters = CONVERT(xml,
    N'<TSQLQueryCollector>  
        <Query>  
          <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
          <OutputTable>cdc_log_scan_data</OutputTable>  
        </Query>  
      </TSQLQueryCollector>');  
EXEC dbo.sp_syscollector_create_collection_item  
@collection_set_id = @collection_set_id,  
@collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
@name = ' CDC Performance Data Collector',  
@frequency = 5,
@parameters = @parameters,  
@collection_item_id = @collection_item_id output;
GO

I get the following error:

Msg 14684, Level 16, State 1, Procedure dbo.sp_syscollector_create_collection_item, Line 144 [Batch Start Line 0]
Caught error#: 6913, Level: 16, State: 1, in Procedure: -, Line: 1, with Message: XML Validation: Declaration not found for element 'TSQLQueryCollector'. Location: /*:TSQLQueryCollector[1]

Any help/feedback appreciated!

Answer 1

Replace:

    N'<TSQLQueryCollector>  
        <Query>  
          <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
          <OutputTable>cdc_log_scan_data</OutputTable>  
        </Query>  
      </TSQLQueryCollector>'

with:

N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
       <Query>
       <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>
       <OutputTable>cdc_log_scan_data</OutputTable>
       </Query>
       </ns:TSQLQueryCollector>'

Answer 2

Hi @Victor

Check if the TSQLQueryCollector XML schema is properly configured and loaded. If it is missing, you may need to recreate the Data Collector configuration.

You can check the Data Collector configuration in SSMS under Management > Data Collection.

Configure the management data warehouse (SQL Server Management Studio)

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".