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