Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Used to modify the properties of a user-defined collection set or to rename a user-defined collection set.
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).
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' ]
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:
A. Renaming a collection set
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
B. Changing the collection mode from non-cached to cached
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
C. Changing other collection set parameters
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