發行項
09/24/2010

一般 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 。
sqlvariant 類型會由預設的處理所處理 (針對 SSIS 未直接處理的資料行)。這表示這些資料行會視為 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 查詢收集器型別。

變更記錄

更新的內容
更正一般 T-SQL 查詢收集器型別的結構描述和所有範例。

請參閱

共用方式為