Export (0) Print
Expand All

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.

Caution note Caution

In cases where the Windows account configured as a proxy is a non-interactive or interactive user that has not yet logged in, the profile directory will not exist, and the creation of the staging directory will fail. Therefore, if you are using a proxy account on a domain controller, you must specify an interactive account that has been used at least once in order to assure that the profile directory has been created.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

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 ]

[ @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 Create a SQL Server Agent Proxy.

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

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;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft