Edit

Add a collection item to a collection set (Transact-SQL)

  • Article

Applies to: SQL Server

You can add a collection item to an existing collection set using the stored procedures that are provided with the data collector.

Carry out the following steps using Query Editor in SQL Server Management Studio.

Add a collection item to a collection set

  1. Stop the collection set that you want to add the item to by running the sp_syscollector_stop_collection_set stored procedure. For example, to stop a collection set that is named "Test Collection Set", run the following statements:

    USE msdb;
GO

DECLARE @csid INT;

SELECT @csid = collection_set_id
FROM syscollector_collection_sets
WHERE name = 'Test Collection Set';

SELECT @csid;

EXEC dbo.sp_syscollector_stop_collection_set @collection_set_id = @csid;

    Note

    You can also stop the collection set by using Object Explorer in SQL Server Management Studio. For more information, see Start or stop a collection set.

  2. Declare the collection set that you want to add the collection item to. The following code provides an example of how to declare the collection set ID.

    DECLARE @collection_set_id_1 INT;

SELECT @collection_set_id_1 = collection_set_id
FROM msdb.dbo.syscollector_collection_sets
WHERE name = N'Test Collection Set';-- name of collection set

  3. Declare the collector type. The following code provides an example of how to declare the Generic T-SQL Query collector type.

    DECLARE @collector_type_uid_1 UNIQUEIDENTIFIER;

SELECT @collector_type_uid_1 = collector_type_uid
FROM msdb.dbo.syscollector_collector_types
WHERE name = N'Generic T-SQL Query Collector Type';

    You can run the following code to obtain a list of the installed collector types:

    USE msdb;
GO

SELECT * FROM syscollector_collector_types;
GO

  4. Run the sp_syscollector_create_collection_item stored procedure to create the collection item. You must declare the schema for the collection item so that it maps to the required schema for the desired collector type. The following example uses the Generic T-SQL Query input schema.

    DECLARE @collection_item_id INT;

EXEC msdb.dbo.sp_syscollector_create_collection_item @name = N'OS Wait Stats', --name of collection item
    @parameters = N'
        <ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
         <Query>
          <Value>select * from sys.dm_os_wait_stats</Value>
          <OutputTable>os_wait_stats</OutputTable>
        </Query>
        </ns:TSQLQueryCollector>',
@collection_item_id = @collection_item_id OUTPUT,
@frequency = 60,
@collection_set_id = @collection_set_id_1, --- Provides the collection set ID number
@collector_type_uid = @collector_type_uid_1;-- Provides the collector type UID

SELECT @collection_item_id;

  5. Before starting the updated collection set, run the following query to verify that the new collection item has been created:

    USE msdb;
GO
SELECT * from syscollector_collection_sets;
SELECT * from syscollector_collection_items;
GO

    The collection sets and their collection items are displayed in the Results tab.

Related content