Freigeben über

  • Artikel

Generischer T-SQL-Abfrageauflistertyp

Mit dem generischen T-SQL-Abfragesammlertyp wird eine vom Benutzer bereitgestellte Transact-SQL-Anweisung als Eingabeparameter ausgeführt, die Ausgabe der Abfrage gespeichert und die Ausgabe anschließend in das Verwaltungs-Data Warehouse hochgeladen. Dieser Sammlertyp wird im Verwaltungs-Data Warehouse in der core.supported_collector_types-Sicht registriert.

Dieser Sammler hat die folgenden Eingabeparameter:

  • Value -Die Transact-SQL-Abfrage. Sie können mehrere Abfragen als Eingabe angeben.

  • OutputTable - Geben Sie den Namen der Tabelle an, die zum Speichern der Abfrageergebnisse verwendet wird, bevor diese in das Verwaltungs-Data Warehouse hochgeladen werden.

  • Database - Geben Sie die Datenbank bzw. die Datenbanken an, für die die Abfrage ausgeführt werden soll. Sie können mehrere Datenbanken nach Namen angeben, oder Sie können das Platzhalterzeichen * verwenden, um alle Datenbanken auf dem Server anzugeben. Wenn Sie keinen Wert für die Database angeben, wird die Abfrage für alle Systemdatenbanken ausgeführt.

Generisches T-SQL-Abfrageeingabeschema

Das Schema für die generische T-SQL-Abfragesammlereingabe lautet wie folgt.

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

Die folgenden Codebeispiele zeigen, wie das vorherige Schema verwendet werden kann. Ein vollständiges Codebeispiel finden Sie unter Vorgehensweise: Erstellen eines benutzerdefinierten Auflistsatzes, der einen generischen T-SQL-Abfrageauflistertyp verwendet.

Beispiel 1

Im folgenden Beispiel werden das System und die Benutzerdatenbanken abgefragt. Anschließend werden die Ergebnisse in die Tabelle custom_snapshots.VerifyDbName ausgegeben.

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

Beispiel 2

Im folgenden Beispiel werden die drei Systemdatenbanken abgefragt und die Ergebnisse in die Tabelle custom_snapshots.MyOutputTable1 ausgegeben.

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

Beispiel 3

Im folgenden Beispiel werden nur die Benutzerdatenbanken abgefragt und die Ergebnisse in die Tabellen custom_snapshots.MyOutputTable2 und custom_snapshots.MyOutputTable3 ausgegeben.

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

Verwendungsmöglichkeiten für separate Sammelelemente

Im vorherigen Beispiel wurden alle Abfragen für den gleichen Datenbanksatz ausgeführt. Wenn Sie unterschiedliche Abfragen für verschiedene Datenbanken ausführen müssen, müssen Sie für jede Kombination aus Abfrage und Datenbank jeweils ein separates Sammelelement erstellen.

Ein anderes Szenario, bei dem verschiedene Sammelelemente erforderlich sind, besteht aus zwei Datenbanken mit demselben Tabellennamen, aber mit einem anderen Schema. In Beispiel 4 sind die in diesem Szenario verwendeten Sammelelemente dargestellt.

Beispiel 4

Erstellen Sie das Sammelelement für die erste Datenbank 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>

Erstellen Sie das Sammelelement für die zweite Datenbank 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>

Verarbeiten und Ausgeben

Die Metadaten der Abfrageausgabe sind von der vom Benutzer angegebenen Transact-SQL-Anweisung abhängig, die Spaltenzuordnungen in der Datenflussquelle oder in dem Datenflussziel werden jedoch zur Entwurfszeit erstellt. Dies bedeutet, dass für jedes Sammelelement, das diesen Sammlertyp verwendet, ein angepasstes Paket erforderlich ist, das zur Laufzeit generiert und auf das Format seines Schemas zugeschnitten wurde.

Der Datensammler verwendet einen benutzerdefinierten Task, um ein Sammlungspaket und ein Paket zum Hochladen zu erstellen, wenn ein neues Sammelelement empfangen wird, oder wenn es eine Aktualisierung der Eingabeparameter für ein vorhandenes Sammelelement gibt. Diese Pakete werden in msdb gespeichert, sodass sie wiederverwendet werden können.

Das Verhalten beim Sammeln und Hochladen von Daten entspricht dem unter Architektur und Verarbeitung des Datensammler beschriebenen Verhalten, es wird jedoch für den generischen T-SQL-Abfragesammlertyp etwas anders implementiert.

Die folgenden Verwaltungs-Data Warehouse-Tabellen werden zum Speichern der von dem generischen T-SQL-Abfragesammlertyp aufgelisteten Daten verwendet:

  • core.snapshots – Diese Tabelle identifiziert jede neue Momentaufnahme.

  • core.snapshot_timetable - Die Snapshotzeit wird in einer separaten Tabelle gespeichert, da viele Snapshots zur nahezu gleichen Zeit stattfinden können.

  • core.source.info – In dieser Tabelle werden Informationen über die Datenquelle und die Daten gespeichert.

Einschränkungen

Es gibt bestimmte Einschränkungen, auf die beim Verwenden des generischen T-SQL-Abfragesammlertyps geachtet werden muss:

  • Die folgenden Spaltennamen sind für den Datensammler reserviert: snapshot_time, snapshot_id und database_name. Diese Namen können von keinen von benutzerdefinierten Sammlungssätzen erstellten Tabellen verwendet werden. Wenn versucht wird, sie zu verwenden, wird ein Fehler zurückgegeben.

  • Eine Spalte vom Typ sysname wird in nvarchar(128) konvertiert, wenn sie in das Verwaltungs-Data Warehouse kopiert wird. Während der Datensammlung konvertiert SQL Server 2008 Integration Services (SSIS) Datenbankdatentypen in SSIS-Datentypen (z. B. sysname wird zu DT_WSTR, und nvarchar(len) wird zu DT_WSTR). Diese Konvertierung wird im Datenflusstask der OLEDB-Quelle ausgeführt. Während des Hochladens der Daten liest der Datensammler Daten aus dem Cache als SSIS-Datentyp, und die Daten werden als nvarchar(128) behandelt, was der Funktionalität von sysname entspricht.

  • Eine Spalte vom Typ char(N) wird zu varchar(N), wenn sie in das Verwaltungs-Data Warehouse kopiert wird (char(N) kann in varchar(N) passen). Abgesehen davon, dass die char-Speichergröße im Gegensatz zur variablen varchar-Speichergröße fest ist, werden diese Typen als funktionell identisch behandelt.

  • Eine Spalte vom Typ varbinary wird zu binary, wenn sie in das Verwaltungs-Data Warehouse kopiert wird.

  • Eine Spalte vom Typ decimal wird zu numeric, wenn sie in das Verwaltungs-Data Warehouse kopiert wird.

  • Eine Spalte vom Typ nchar wird zu nvarchar , wenn sie in das Verwaltungs-Data Warehouse kopiert wird.

  • Der Typ sqlvariant wird von der Standardverarbeitung für Spalten behandelt, die SSIS nicht direkt behandelt. Dies bedeutet, dass die Spalten als nvarchar(255) behandelt werden und jede Konvertierung dem Datenanbieter überlassen wird.

    HinweisHinweis

    In diesem Fall wird die Spalte mit einer Standardlänge von 255 Zeichen erstellt. Er kann jedoch auf 4000 Zeichen vergrößert werden.

  • Alle von einer Transact-SQL-Abfrage zurückgegebenen Spalten müssen einen Namen haben. So funktioniert select 1 beispielsweise nicht, select 1 as one hingegen schon.

  • Die folgenden Datentypen werden von SSIS nicht unterstützt und können nicht als Spalten in eine beliebige Ausgabetabelle einbezogen werden, die von einem Sammlungssatz generiert wurde, der den generischen T-SQL-Abfragesammlertyp verwendet:

    • image

    • text

    • ntext

    • XML

  • Alle vom generischen T-SQL-Abfragesammlertyp ausgeführten Abfragen müssen ein einzelnes Resultset zurückgeben.

  • Abfragen lokaler temporärer Tabellen werden nur unterstützt, wenn sie zuerst als Teil desselben Batches deklariert werden. Abfragen globaler temporärer Tabellen werden vollständig unterstützt.

  • Es werden keine Indizes, privaten Schlüssel, Fremdschlüssel oder andere Einschränkungen in die Zieltabellen des Verwaltungs-Data Warehouse übertragen. Dies liegt daran, dass dieselben Daten mehrfach abgefragt werden, und die Daten von mehreren Computern in einer einzelnen Tabelle zusammengefasst werden können.

  • Etwaige andere Beschränkungen hinsichtlich des Typs unterstützter Abfragen, die für den Datenflusstask der OLE DB-Quelle von SSIS gelten, sind auch für den generischen T-SQL-Abfragesammlertyp gültig.