| title | sp_syscollector_update_collection_set (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 2dccc3cd-0e93-4e3e-a4e5-8fe89b31bd63 | ||
| caps.latest.revision | 28 | ||
| author | JennieHubbard | ||
| ms.author | jhubbard | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Used to modify the properties of a user-defined collection set or to rename a user-defined collection set.
Warning
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: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version). |
Transact-SQL Syntax Conventions
sp_syscollector_update_collection_set
[ [ @collection_set_id = ] collection_set_id ]
, [ [ @name = ] 'name' ]
, [ [ @new_name = ] 'new_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_uid' ]
, [ [ @logging_level = ] logging_level ]
, [ [ @description = ] 'description' ]
[ @collection_set_id = ] collection_set_id
Is the unique local identifier for the collection set. collection_set_id is int and must have a value if name is NULL.
[ @name = ] 'name'
Is the name of the collection set. name is sysname and must have a value if collection_set_id is NULL.
[ @new_name = ] 'new_name'
Is the new name for the collection set. new_name is sysname, and if used, cannot be an empty string. new_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.
[ @collection_mode = ] collection_mode
Is the type of data collection to use. 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.
If changing from non-cached mode to cached mode (0), you must also specify either schedule_uid or schedule_name.
[ @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. days_until_expiration must be 0 or a positive integer.
[ @proxy_id = ] proxy_id
Is the unique identifier for a [!INCLUDEssNoVersion] Agent proxy account. proxy_id is int.
[ @proxy_name = ] 'proxy_name'
Is the name of the proxy. proxy_name is sysname and is nullable.
[ @schedule_uid = ] 'schedule_uid'
Is the GUID that points to a schedule. schedule_uid is uniqueidentifier.
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 and is nullable. 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 [!INCLUDEssIS] 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 [!INCLUDEssIS]
2 - Level-1 logging and detailed event information from [!INCLUDEssIS].
The default value for logging_level is 1.
[ @description = ] 'description'
Is the description of the collection set. description is nvarchar(4000).
0 (success) or 1 (failure)
sp_syscollector_update_collection_set must be run in the context of the msdb system database.
Either collection_set_id or name must have a value, both cannot be NULL. To obtain these values, query the syscollector_collection_sets system view.
If the collection set is running, you can only update schedule_uid and description. To stop the collection set, use sp_syscollector_stop_collection_set.
Requires membership in the dc_admin or the dc_operator (with EXECUTE permission) fixed database role to execute this procedure. Although dc_operator can run this stored procedure, members of this role are limited in the properties that they can change. The following properties can only be changed by dc_admin:
-
@new_name
-
@target
-
@proxy_id
-
@description
-
@collection_mode
-
@days_until_expiration
The following example renames a user-defined collection set.
USE msdb;
GO
EXECUTE dbo.sp_syscollector_update_collection_set
@name = N'Simple collection set test 1',
@new_name = N'Collection set test 1 in cached mode';
GO
The following example changes the collection mode from non-cached mode to cached mode. This change requires that you specify a schedule ID or schedule name.
USE msdb;
GO
EXECUTE dbo.sp_syscollector_update_collection_set
@name = N'Collection set test 1 in cached mode',
@collection_mode = 0,
@schedule_uid = 'C7022AF3-51B8-4011-B159-64C47C88FF70';
-- alternatively, use @schedule_name.
-- @schedule_name = N'CollectorSchedule_Every_15min;
GO
The following example updates various properties of the collection set named "Simple collection set test 2'.
USE msdb;
GO
EXEC dbo.sp_syscollector_update_collection_set
@name = N'Simple collection set test 2',
@collection_mode = 1,
@days_until_expiration = 5,
@description = N'This is a test collection set that runs in noncached mode.',
@logging_level = 0;
GO
System Stored Procedures (Transact-SQL)
Data Collection
syscollector_collection_sets (Transact-SQL)
dbo.sysschedules (Transact-SQL)