项目
04/01/2012

一般 T-SQL 查询收集器类型

一般 T-SQL 查询收集器类型执行用户提供的作为输入参数的 Transact-SQL 语句，保存查询输出，然后将输出上载到管理数据仓库。此收集器类型在管理数据仓库中的 core.supported_collector_types 视图中注册。

此收集器具有以下输入参数：

Value - Transact-SQL 查询。您可以提供多个查询作为输入参数。
OutputTable - 提供在将查询结果上载到管理数据仓库之前保存查询结果的表的名称。
Database - 指定要对其运行查询的一个或多个数据库。可以使用名称指定数据库，也可以使用 * 通配符指定服务器上的所有数据库。如果未为 Database 提供值，则将对所有系统数据库运行查询。

一般 T-SQL 查询输入架构

一般 T-SQL 查询收集器输入架构如下。

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
  <xs:element name="TSQLQueryCollector">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Query" minOccurs="1" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Value" type="xs:string" />
              <xs:element name="OutputTable" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Databases" minOccurs="0" maxOccurs="1">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Database" minOccurs="0" maxOccurs="unbounded" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="UseSystemDatabases" type="xs:boolean" use="optional" />
            <xs:attribute name="UseUserDatabases" type="xs:boolean" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

下面的代码示例演示如何使用该架构。有关完整的代码示例，请参阅如何创建使用一般 T-SQL 查询收集器类型的自定义收集组。

示例 1

下面的示例查询系统数据库和用户数据库，然后将结果放入 custom_snapshots.VerifyDbName 表中。

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT DB_NAME() as db_name</Value>
    <OutputTable>VerifyDbName</OutputTable>
  </Query>
  <Databases UseSystemDatabases="true" UseUserDatabases="true" /> 
</ns:TSQLQueryCollector>

示例 2

下面的示例查询三个系统数据库，然后将结果放入 custom_snapshots.MyOutputTable1 表中。

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.objects</Value>
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>model</Database>
    <Database>tempdb</Database>
    <Database>master</Database>
  </Databases>
</ns:TSQLQueryCollector>

示例 3

下面的示例仅查询用户数据库，然后将结果放入 custom_snapshots.MyOutputTable2 和 custom_snapshots.MyOutputTable3 这两个表中。

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.dm_tran_database_transactions</Value>
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Query>
    <Value>SELECT * FROM sys.dm_db_file_space_usage</Value>
    <OutputTable>MyOutputTable3</OutputTable>
  </Query>
  <Databases UseSystemDatabases="false" UseUserDatabases="true" />
</ns:TSQLQueryCollector>

何时使用单独的收集项

在上面的示例中，所有查询均针对同一组数据库执行。如果需要为不同的数据库执行不同的查询，则必须为各查询数据库组合创建单独的收集项。

另一种需要不同收集项的情况是：两个数据库具有相同的表名称但具有不同的架构。示例 4 演示了用于处理这种情况的收集项。

示例 4

为第一个数据库 db1 创建收集项。

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>db1</Database>
  </Databases>
</ns:TSQLQueryCollector>

为第二个数据库 db2 创建收集项。

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Databases>
    <Database>db2</Database>
  </Databases>
</ns:TSQLQueryCollector>

处理和输出

查询输出的元数据取决于用户指定的 Transact-SQL 语句，但数据流源或数据流目标中的列映射在设计时即已创建。这意味着使用此收集器类型的每个收集项均需要一个自定义包，此包在运行时生成，并针对其架构格式定制。

在收到新的收集项后（或者如果现有收集项的输入参数进行了更新），数据收集器将使用自定义任务来创建收集包和上载包。这些包都保存在 msdb 中，因此可以重新使用。

对于一般 T-SQL 查询收集器类型而言，数据收集和上载行为与数据收集器体系结构和处理中介绍的相似，但实现则稍有不同。

下面的管理数据仓库表用于存储由一般 T-SQL 查询收集器类型收集的数据：

core.snapshots - 此表用于标识每个新快照。
core.snapshot_timetable - 快照时间存储在单独的表中，因为几乎在同一时间可产生许多快照。
core.source.info - 此表用于存储与数据源及数据相关的信息。

限制

使用一般 T-SQL 查询收集器类型时，应注意某些限制：

为数据收集器保留以下列名称：snapshot_time、snapshot_id 和 database_name。由自定义收集组创建的任何表均不能使用这些名称。如果尝试使用这些名称则将返回一个错误。
在将类型为 sysname 的列复制到管理数据仓库中时，其类型将转换为 nvarchar(128)。在数据收集过程中，SQL Server 2008 Integration Services (SSIS) 将数据库数据类型转换为 SSIS 数据类型（例如，sysname 变为 DT_WSTR，nvarchar(len) 变为 DT_WSTR）。此转换发生在 OLE DB 源数据流任务内。在数据上载过程中，数据收集器按照 SSIS 数据类型从缓存中读取数据并将数据视为 nvarchar(128) 类型，该类型的作用与 sysname 类型等效。
在将类型为 char(N) 的列复制到管理数据仓库时，其类型变为 varchar(N)（char(N) 可容纳在 varchar(N) 中）。char 存储大小是固定的，尽管如此，varchar 存储大小却是可变的；这些类型被视为是作用相同的类型。
在将类型为 varbinary 的列复制到管理数据仓库时，其类型变为 binary。
在将类型为 decimal 的列复制到管理数据仓库时，其类型变为 numeric。
在将类型为 nchar 的列复制到管理数据仓库中时，其类型变为 nvarchar。
使用 SSIS 不直接处理的列的默认处理方式来处理 sqlvariant 类型。这意味着将列的类型视为 nvarchar(255)，从而使数据访问接口来进行所有转换。

注意

在这种情况下，列创建时的默认长度为 255 个字符。但可以更改为 4000 个字符。
Transact-SQL 查询返回的所有列都必须具有名称。例如，select 1 不适用，但 select 1 as one 适用。
SSIS 不支持以下数据类型，并且这样的数据不会作为列出现在由使用一般 T-SQL 查询收集器类型的收集组生成的任何输出表中：
- image
- text
- ntext
- XML
由一般 T-SQL 查询收集器类型执行的所有查询都必须返回单个结果集。
只有首先将局部临时表查询声明为同一批处理的一部分才支持局部临时表查询。完全支持全局临时表查询。
未将索引、私钥、外键或任何其他约束移至管理数据仓库中的目标表。这是因为对同样的数据进行了多次查询，并且单个表中的数据可能来自多个计算机。
适用于 SSIS 中的 OLE DB 源数据流任务且与所支持的查询类型有关的其他任何限制亦适用于一般 T-SQL 查询收集器类型。

注意
在这种情况下，列创建时的默认长度为 255 个字符。但可以更改为 4000 个字符。

请参阅

通过