Creates a new collection set. You can use this stored procedure to create a custom collection set for data collection.
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.
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
1 - level-0 logging and:
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)
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;
Create a Custom Collection Set That Uses the Generic T-SQL Query Collector Type (Transact-SQL)
Data Collector Stored Procedures (Transact-SQL)