Export (0) Print
Expand All

core.sp_update_data_source (Transact-SQL)

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).

Updates an existing row or inserts a new row in the management data warehouse core.source_info_internal table. This procedure is called by the data collector run-time component every time an upload package starts uploading data to the management data warehouse.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

core.sp_update_data_source [ @collection_set_uid = ] 'collection_set_uid' 
    ,[ @machine_name = ] 'machine_name'
    , [ @named_instance = ] 'named_instance'
    , [ @days_until_expiration = ] days_until_expiration 
    , [ @source_id = ] source_id OUTPUT

[ @collection_set_uid = ] 'collection_set_uid'

The GUID for the collection set. collection_set_uid is uniqueidentifier, with no default value. To obtain the GUID, query the dbo.syscollector_collection_sets view in the msdb database.

[ @machine_name = ] 'machine_name'

The name of the server that the collection set resides on. machine_name is sysname with no default value.

[ @named_instance = ] 'named_instance'

The name of the instance for the collection set. named_instance is sysname, with no default value.

Note Note

named_instance must be the fully qualified instance name, which consists of the computer name and the instance name in the form computername\instancename.

[ @days_until_expiration = ] days_until_expiration

The number of days remaining in the snapshot data retention period. days_until_expiration is smallint.

[ @source_id = ] source_id

The unique identifier for the source of the update. source_id is int and is returned as OUTPUT.

0 (success) or 1 (failure)

Every time an upload package starts uploading data to the management data warehouse, the data collector run-time component calls core.sp_update_data_source. The core.source_info_internal table is updated if one of the following changes has happened since the last upload:

  • A new collection set was added.

  • The value for days_until_expiration has changed.

Requires membership in the mdw_writer (with EXECUTE permission) fixed database role.

The following example updates the data source (in this case the Disk Usage collection set), sets the number of days until expiration, and returns the identifier for the source. In the example, the default instance is used.

USE <management_data_warehouse>;
DECLARE @source_id int;
EXEC core.sp_update_data_source 
@collection_set_uid = '7B191952-8ECF-4E12-AEB2-EF646EF79FEF', 
@machine_name = '<computername>',
@named_instance = 'MSSQLSERVER',
@days_until_expiration = 10,
@source_id = @source_id OUTPUT;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft