sp_syscollector_set_warehouse_instance_name (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Specifies the instance name for the connection string used to connect to the management data warehouse.

Topic link icon Transact-SQL Syntax Conventions


sp_syscollector_set_warehouse_instance_name [ @instance_name = ] 'instance_name'  


[ @instance_name = ] 'instance_name'
Is the instance name. instance_name is sysname and defaults to the local instance if NULL.

NOTE: instance_name must be the fully qualified instance name, which consists of the computer name and the instance name in the form computerName\instanceName.

Return Code Values

0 (success) or 1 (failure)


You must disable the data collector before changing this data collector-wide configuration. This procedure fails if the data collector is enabled.

To view the current instance name, query the syscollector_config_store system view.


Requires membership in the dc_admin (with EXECUTE permission) fixed database role to execute this procedure.


The following example illustrates how to configure the data collector to use a management data warehouse instance on a remote server. In this example the remote server is named RemoteSERVER and the database is installed on the default instance.

USE msdb;  
EXEC sp_syscollector_set_warehouse_instance_name N'RemoteSERVER'; -- the default instance is assumed on the remote server  

See Also

Data Collector Stored Procedures (Transact-SQL)
syscollector_config_store (Transact-SQL)