• Article

sp_syscollector_create_collection_set (Transact-SQL)

Creates a new collection set. You can use this stored procedure to create a custom collection set for data collection.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_syscollector_create_collection_set 
      [ @name = ] 'name'
    , [ [ @target = ] 'target' ]
    , [ [ @collection_mode = ] collection_mode ]
    , [ [ @days_until_expiration = ] days_until_expiration ]
    , [ [ @proxy_id = ] proxy_id ]
    , [ [ @proxy_name = ] 'proxy_name' ]
    , [ [ @schedule_uid = ] 'schedule_uid' ]
    , [ [ @schedule_name = ] 'schedule_name' ]
    , [ [ @logging_level = ] logging_level ]
    , [ [ @description = ] 'description' ]
    , [ @collection_set_id = ] collection_set_id OUTPUT 
    , [ [ @collection_set_uid = ] 'collection_set_uid' OUTPUT ]

Arguments

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

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

  • [ @target = ] 'target'
    Reserved for future use. name is nvarchar(128) with a default value of NULL.

  • [ @collection\_mode = ] collection_mode
    Specifies the manner in which the data is collected and stored. collection_mode is smallint and can have one of the following values:

    0 - Cached mode. Data collection and upload are on separate schedules. Specify cached mode for continuous collection.

    1 - Non-cached mode. Data collection and upload is on the same schedule. Specify non-cached mode for ad hoc collection or snapshot collection.

    The default value for collection_mode is 0. When collection_mode is 0, schedule_uid or schedule_name must be specified.

  • [ @days\_until\_expiration = ] days_until_expiration
    Is the number of days that the collected data is saved in the management data warehouse. days_until_expiration is smallint with a default value of 730 (two years). days_until_expiration must be 0 or a positive integer.

  • [ @proxy\_id = ] proxy_id
    Is the unique identifier for a SQL Server Agent proxy account. proxy_id is int with a default value of NULL. If specified, proxy_name must be NULL. To obtain proxy_id, query the sysproxies system table. The dc_admin fixed database role must have permission to access the proxy. For more information, see Creating SQL Server Agent Proxies.

  • [ @proxy\_name = ] 'proxy_name'
    Is the name of the proxy account. proxy_name is sysname with a default value of NULL. If specified, proxy_id must be NULL. To obtain proxy_name, query the sysproxies system table.

  • [ @schedule\_uid = ] 'schedule_uid'
    Is the GUID that points to a schedule. schedule_uid is uniqueidentifier with a default value of NULL. If specified, schedule_name must be NULL. To obtain schedule_uid, query the sysschedules system table.

    When collection_mode is set to 0, schedule_uid or schedule_name must be specified. When collection_mode is set to 1, schedule_uid or schedule_name is ignored if specified.

  • [ @schedule\_name = ] 'schedule_name'
    Is the name of the schedule. schedule_name is sysname with a default value of NULL. If specified, schedule_uid must be NULL. To obtain schedule_name, query the sysschedules system table.

  • [ @logging\_level = ] logging_level
    Is the logging level. logging_level is smallint with one of the following values:

    0 - log execution information and SSIS events that track:

    • Starting/stopping collection sets

    • Starting/stopping packages

    • Error information

    1 - level-0 logging and:

    • Execution statistics

    • Continuously running collection progress

    • Warning events from SSIS

    2 - level-1 logging and detailed event information from SSIS

    The default value for logging_level is 1.

  • [ @description = ] 'description'
    Is the description of the collection set. description is nvarchar(4000) with a default value of NULL.

  • [ @collection\_set\_id = ] collection_set_id
    Is the unique local identifier for the collection set. collection_set_id is int with OUTPUT and is required.

  • [ @collection\_set\_uid = ] 'collection_set_uid'
    Is the GUID for the collection set. collection_set_uid is uniqueidentifier with OUTPUT with a default value of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

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

Permissions

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

Examples

A. Creating a collection set by using default values

The following example creates a collection set by specifying only the required parameters. @collection\_mode is not required, but the default collection mode (cached) requires specifying either a schedule ID or schedule name.

USE msdb;
GO
DECLARE @collection_set_id int;
EXECUTE dbo.sp_syscollector_create_collection_set
    @name = N'Simple collection set test 1',
    @description = N'This is a test collection set that runs in non-cached mode.',
    @collection_mode = 1,
    @collection_set_id = @collection_set_id OUTPUT;
GO

B. Creating a collection set by using specified values

The following example creates a collection set by specifying values for many of the parameters.

USE msdb;
GO
DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier;
SET @collection_set_uid = NEWID();
EXEC dbo.sp_syscollector_create_collection_set
    @name = N'Simple collection set test 2',
    @collection_mode = 0,
    @days_until_expiration = 365,
    @description = N'This is a test collection set that runs in cached mode.',
    @logging_level = 2,
    @schedule_name = N'CollectorSchedule_Every_30min',
    @collection_set_id = @collection_set_id OUTPUT,
    @collection_set_uid = @collection_set_uid OUTPUT;