次の方法で共有

  • [アーティクル]

ジェネリック T-SQL Query コレクター型

ジェネリック T-SQL Query コレクター型は、入力パラメーターとして指定された Transact-SQL ステートメントを実行し、クエリの出力を保存した後、それを管理データ ウェアハウスにアップロードします。このコレクター型は、管理データ ウェアハウスの core.supported_collector_types ビューに登録されています。

このコレクターには次の入力パラメーターがあります。

  • Value - Transact-SQL クエリです。複数のクエリを入力として指定できます。

  • OutputTable - クエリの結果を管理データ ウェアハウスにアップロードする前に保存するためのテーブルの名前を指定します。

  • Database - クエリの実行対象となるデータベースを指定します。名前でデータベースを指定することも、ワイルドカード文字 * を使用してサーバー上のすべてのデータベースを指定することもできます。Database の値を指定しない場合は、すべてのシステム データベースに対してクエリが実行されます。

ジェネリック T-SQL Query の入力スキーマ

ジェネリック T-SQL Query コレクターの入力のスキーマを以下に示します。

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

<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 Query コレクタ型を使用するカスタム コレクション セットを作成する方法」を参照してください。

例 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>

<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

次のコード例は、3 つのシステム データベースに対してクエリを実行し、結果を 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>

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

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

個別のコレクション アイテムを使用する場合

上の例では、すべてのクエリが同じデータベース セットに対して実行されています。各データベースに対して異なるクエリを実行するには、クエリとデータベースの組み合わせごとに個別のコレクション アイテムを作成する必要があります。

ここでは、異なるコレクション アイテムを必要とするシナリオの例として、テーブル名が同じでスキーマが異なる 2 つのデータベースの例を紹介します。例 4 は、このシナリオを処理するためのコレクション アイテムを示しています。

例 4

1 つ目のデータベース (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>

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

2 つ目のデータベース (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>

<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 Query コレクター型では実装が多少異なります。

ジェネリック T-SQL Query コレクター型によって収集されたデータは、管理データ ウェアハウスの以下のテーブルを使用して格納されます。

  • core.snapshots - このテーブルはそれぞれの新しいスナップショットを識別します。

  • core.snapshot_timetable - 多数のスナップショットがほぼ同時に発生する可能性があるため、スナップショット時間は別のテーブルに格納されます。

  • core.source.info - データ ソースとデータに関する情報を格納します。

制限事項

ジェネリック T-SQL Query コレクター型を使用する際には以下の制限事項に注意してください。

  • 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 文字の列が既定で作成されます。しかし、列の長さは 4,000 文字に変更できます。

  • Transact-SQL クエリによって返されるすべての列に名前が必要です。たとえば、select 1 は使用できませんが、select 1 as one は使用できます。

  • 以下のデータ型は SSIS でサポートされていません。したがって、ジェネリック T-SQL Query コレクター型を使用するコレクション セットによって生成される出力テーブルの列として含めることはできません。

    • image

    • text

    • ntext

    • XML

  • ジェネリック T-SQL Query コレクター型によって実行されるクエリはすべて単一の結果セットを返す必要があります。

  • ローカル一時テーブルのクエリは、最初に同じバッチの一部として宣言されている場合にのみサポートされます。グローバル一時テーブルのクエリは完全にサポートされています。

  • インデックス、秘密キー、外部キーなどの制約は、管理データ ウェアハウスのターゲット テーブルには反映されません。これは、同じデータのクエリが複数回実行されて、複数のコンピューターのデータが 1 つのテーブルに格納される可能性があるためです。

  • そのほか、SSIS の OLE DB ソース データ フロー タスクに適用される、サポートされるクエリの種類に関する制限は、すべてジェネリック T-SQL Query コレクター型にも適用されます。