[ @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.

0 (success) or 1 (failure)

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

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

A. Creating a collection set by using default values

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

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;

Tasks

Reference

Other Resources