December 6, 2012

Data Collector Views

Se more view version maps here: Microsoft Sql System View Version Maps

syscollector_collection_items

Returns information about an item in a collection set.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collection_set_id   • • • int Identifies the collection set. Is not nullable.
collection_item_id   • • • int Identifies an item in the collection set. Is not nullable.
collector_type_uid   • • • uniqueidentifier The GUID used to identify the collector type. Is not nullable.
name   • • • nvarchar(4000) The name of the collection set. Is nullable.
frequency   • • • int The frequency that data is collected by a collection item. Is not nullable.
parameters   • • • xml Describes the parameterization for the collector type associated with the collection item. The XML schema for this collection item is validated with the XML Schema (XSD) stored in the parameter_schema for a particular collector type. Is nullable. For more information, see syscollector_collector_types (Transact-SQL)1.

TSQL

Sql 2008
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2008 R2
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2012
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items

Back to Top


syscollector_collection_sets

Provides information about a collection set, including schedule, collection mode, and its state.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collection_set_id   • • • int The local identifier for the collection set. Is not nullable.
collection_set_uid   • • • uniqueidentifier The globally unique identifier for the collection set. Is not nullable.
name   • • • nvarchar(4000) The name of the collection set. Is nullable.
target   • • • nvarchar(max) Identifies the target for the collection set. Is nullable.
is_system   • • • bit Turned on (1) or off (0) to indicate if the collection set was included with the data collector or if it was added later by the dc_admin. This could be a custom collection set developed in-house or by a third party. Is not nullable.
is_running   • • • bit Indicates whether or not the collection set is running. Is not nullable.
collection_mode   • • • smallint Specifies the collection mode for the collection set. Is not nullable.
Collection mode is one of the following:
0 - Cached mode. Data collection and upload are on separate schedules.
1 - Non-cached mode. Data collection and upload are on the same schedule.
proxy_id   • • • int Identifies the proxy that is used to run the collection set job step. Is nullable.
schedule_uid   • • • uniqueidentifier Provides a pointer to the collection set schedule. Is nullable.
collection_job_id   • • • uniqueidentifier Identifies the collection job. Is nullable.
upload_job_id   • • • uniqueidentifier Identifies the collection upload job. Is nullable.
logging_level   • • • smallint Specifies the logging level (0, 1 or 2). Is not nullable. For more information about logging levels, see Data Collector Logging1.
days_until_expiration   • • • smallint The number of days that the collected data is saved in the management data warehouse. Is not nullable.
description   • • • nvarchar(4000) Describes the collection set. Is nullable.
dump_on_any_error   • • • bit Turned on (1) or off (0) to indicate whether to create an SSIS dump file on any error. Is not nullable.
dump_on_codes   • • • nvarchar(max) Contains the list of SSIS error codes that are used to trigger the dump file. Is nullable.
For more information about how to obtain a dump file, see the How to enable the Sqldumper.exe utility to generate dump files for processes that are related to SQL Server 2005 Integration Services with Service Pack 22 article in the Microsoft Knowledge Base.

TSQL

Sql 2008
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2008 R2
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2012
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets

Back to Top


syscollector_collector_types

Provides information about a collector type for a collection item.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
collector_type_uid   • • • uniqueidentifer The GUID for a collection type. Is not nullable.
name   • • • sysname The name of the collection type. Is not nullable.
parameter_schema   • • • xml The XML schema that describes what the configuration for the specified collector type looks like. This XML schema is used to validate the actual XML configuration associated with a particular collection item instance. Is nullable.
parameter_formatter   • • • xml Determines the template to use to transform the XML for use in the collection set property page. Is nullable.
collection_package_id   • • • uniqueidentifer The GUID for a collection package. Is not nullable.
collection_package_path   • • • nvarchar(4000) Provides the path to the collection package. Is nullable.
collection_package_name   • • • sysname The name of the collection package. Is not nullable.
upload_package_id   • • • uniqueidentifer The GUID for the upload package. Is not nullable.
upload_package_path   • • • nvarchar(4000) Provides the path to the upload package. Is nullable.
upload_package_name   • • • sysname The name of the upload package. Is not nullable.
is_system   • • • bit Turned on (1) or off (0) to indicate if the collector type was shipped with the data collector or if it was added later by the dc_admin. This could be a custom type developed in-house or by a third party. Is not nullable.

TSQL

Sql 2008
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2008 R2
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2012
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types

Back to Top


syscollector_config_store

Returns properties that apply to the entire data collector, as opposed to a collection set instance. Each row in this view describes a specific data collector property, such as the name of the management data warehouse, and the instance name where the management data warehouse is located.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
parameter_name   • • • nvarchar(128) The name of the property. Is not nullable.
parameter_value   • • • sql_variant The actual value of the property. Is nullable.

TSQL

Sql 2008
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2008 R2
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2012
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store

Back to Top


syscollector_execution_log

Provides information from the execution log for a collection set or package.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   • • • bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable.
parent_log_id   • • • bigint Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages, so that the call chain is clearly visible.
collection_set_id   • • • int Identifies the collection set or package that this log entry represents. Is not nullable.
collection_item_id   • • • int Identifies a collection item. Is nullable.
start_time   • • • datetime The time that the collection set or package started. Is not nullable.
last_iteration_time   • • • datetime For continuously running packages, the last time that the package captured a snapshot. Is nullable.
finish_time   • • • datetime The time the run completed for finished packages and collection sets. Is nullable.
runtime_execution_mode   • • • smallint Indicates whether the collection set activity was collecting data or uploading data. Is nullable.
Values are:
0 = Collection
1 = Upload
status   • • • smallint Indicates the current status of the collection set or package. Is not nullable.
Values are:
0 = running
1 = finished
2 = failed
operator   • • • nvarchar(128) Identifies who started the collection set or package. Is not nullable.
package_id   • • • uniqueidentifier Identifies the collection set or package that generated this log. Is nullable.
package_name   • • • nvarchar(4000) The name of the package that generated this log. Is nullable.
package_execution_id   • • • uniqueidentifier Provides a link to the SSIS log table. Is nullable.
failure_message   • • • nvarchar(2048) If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function.

TSQL

Sql 2008
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2008 R2
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2012
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log

Back to Top


syscollector_execution_log_full

Provides information about a collection set or package when the execution log is full.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   • • • bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is nullable.
parent_log_id   • • • bigint Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages so that the call chain is clearly visible.
name   • • • nvarchar(4000) The name of the collection set or package that this log entry represents. Is nullable.
status   • • • smallint Indicates the current status of the collection set or package. Is nullable.
Values are:
0 = running
1 = finished
2 = failed
runtime_execution_mode   • • • smallint Indicates whether the collection set activity was collecting data or uploading data. Is nullable.
start_time   • • • datetime The time that the collection set or package started. Is nullable.
last_iteration_time   • • • datetime For continuously running packages, the last time that the package captured a snapshot. Is nullable.
finish_time   • • • datetime The time the run completed for finished packages and collection sets. Is nullable.
duration   • • • int The time, in seconds, that the package or collection set has been running. Is nullable.
failure_message   • • • nvarchar(2048) If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function.
operator   • • • nvarchar(128) Identifies who started the collection set or package. Is nullable.
package_execution_id   • • • uniqueidentifier Provides a link to the SSIS log table. Is nullable.
collection_set_id   • • • int Provides a link to the data collection configuration table in msdb. Is nullable.

TSQL

Sql 2008
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2008 R2
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2012
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full

Back to Top


syscollector_execution_stats

Provides information about task execution for a collection set or package.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id   • • • bigint Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable.
task_name   • • • nvarchar(128) The name of the collection set or package task that this information is for. Is not nullable.
execution_row_count_in   • • • int Number of rows processed at the beginning of data flow. Is nullable.
execution_row_count_out   • • • int Number of rows processed at the end of data flow. Is nullable.
execution_row_count_errors   • • • int Number of rows that failed during the data flow. Is nullable.
execution_time_ms   • • • int The time, in milliseconds, required for the task to complete. Is nullable.
log_time   • • • datetime The time that this information was logged. Is not nullable.

TSQL

Sql 2008
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2008 R2
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2012
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats

Back to Top
Indsendt af kl.
Etiketter: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Total Pageviews