sp_syscollector_create_collection_item (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Creates a collection item in a user-defined collection set. A collection item defines the data to be collected and the frequency with which the data is collected.

Topic link icon Transact-SQL Syntax Conventions


      [ @collection_set_id = ] collection_set_id   
    , [ @collector_type_uid = ] 'collector_type_uid'  
    , [ @name = ] 'name'   
    , [ [ @frequency = ] frequency ]  
    , [ @parameters = ] 'parameters'  
    , [ @collection_item_id = ] collection_item_id OUTPUT  


[ @collection_set_id = ] collection_set_id
Is the unique local identifier for the collection set. collection_set_id is int.

[ @collector_type_uid = ] 'collector_type_uid'
Is the GUID that identifies the collector type to use for this item collector_type_uid is uniqueidentifier with no default value.. For a list of collector types, query the syscollector_collector_types system view.

[ @name = ] 'name'
Is the name of the collection item. name is sysname and cannot be an empty string or NULL.

name must be unique. For a list of current collection item names, query the syscollector_collection_items system view.

[ @frequency = ] frequency
Is used to specify (in seconds) how frequently data is collected by this collection item. frequency is int, with a default of 5. The minimum value that can be specified is 5 seconds.

If the collection set is set to non-cached mode, the frequency is ignored because this mode causes both data collection and upload to occur at the schedule specified for the collection set. To view the collection mode of the collection set, query the syscollector_collection_sets system view.

[ @parameters = ] 'parameters'
The input parameters for the collector type. parameters is xml with a default of NULL. The parameters schema must match the parameters schema of the collector type.

[ @collection_item_id = ] collection_item_id
Is the unique identifer that identifies the collection set item. collection_item_id is int and has OUTPUT.

Return Code Values

0 (success) or 1 (failure)


sp_syscollector_create_collection_item must be run in the context of the msdb system database.

The collection set to which the collection item is being added must be stopped before creating the collection item. Collection items cannot be added to system collection sets.


Requires membership in the dc_admin (with EXECUTE permission) fixed database role to execute this procedure.


The following example creates a collection item based on the collection type Generic T-SQL Query Collector Type and adds it to the collection set named Simple collection set test 2. To create the specified collection set, run example B in sp_syscollector_create_collection_set (Transact-SQL).

USE msdb;  
DECLARE @collection_item_id int;  
DECLARE @collection_set_id int = (SELECT collection_set_id   
                                  FROM syscollector_collection_sets  
                                  WHERE name = N'Simple collection set test 2');  
DECLARE @collector_type_uid uniqueidentifier =   
    (SELECT collector_type_uid  
     FROM syscollector_collector_types  
     WHERE name = N'Generic T-SQL Query Collector Type');  
DECLARE @params xml =   
    CONVERT(xml, N'\<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">  
                <Value>SELECT * FROM sys.objects</Value>  
                <Database> UseSystemDatabases = "true"   
                           UseUserDatabases = "true"  

EXEC sp_syscollector_create_collection_item  
    @collection_set_id = @collection_set_id,  
    @collector_type_uid = @collector_type_uid,  
    @name = 'My custom TSQL query collector item',  
    @frequency = 6000,  
    @parameters = @params,  
    @collection_item_id = @collection_item_id OUTPUT;  

See Also

System Stored Procedures (Transact-SQL)
Data Collection
sp_syscollector_update_collection_item (Transact-SQL)
sp_syscollector_delete_collection_item (Transact-SQL)
syscollector_collector_types (Transact-SQL)
sp_syscollector_create_collection_set (Transact-SQL)
syscollector_collection_items (Transact-SQL)