Condividi tramite

  • Articolo

Tipo di agente di raccolta Query T-SQL generico

Il tipo di agente di raccolta Query T-SQL generico esegue un'istruzione Transact-SQL fornita dall'utente come parametro di input, salva l'output dalla query, quindi lo carica nel data warehouse di gestione. Questo tipo di agente di raccolta è registrato nella vista core.supported_collector_types nel data warehouse di gestione.

Tale agente di raccolta ha i seguenti parametri di input:

  • Value - La query Transact-SQL. È possibile fornire più di una query come input.

  • OutputTable - Fornisce il nome della tabella per salvare i risultati della query prima che vengano caricati nel data warehouse di gestione.

  • Database - Specifica il database o i database su cui eseguire la query. È possibile specificare i database tramite il nome o utilizzando il carattere * per specificare tutti i database presenti sul server. Se non si fornisce un valore per Database la query viene eseguita su tutti i database di sistema.

Schema di input di Query T-SQL generico

Lo schema di input dell'agente di raccolta Query T-SQL generico è il seguente:

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

Negli esempi di codice seguenti vengono illustrati i possibili utilizzi dello schema. Per un esempio di codice completo, vedere Procedura: Creazione di un insieme di raccolta personalizzato che utilizza il tipo di agente di raccolta Query T-SQL generico.

Esempio 1

Nell'esempio seguente vengono eseguite query sui database di sistema e sui database utente, quindi i risultati vengono inseriti nella tabella 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>

Esempio 2

Nell'esempio seguente vengono eseguite query sui tre database di sistema,, quindi i risultati vengono inseriti nella tabella 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>

Esempio 3

Nell'esempio seguente vengono eseguite query solo sui database utente, quindi i risultati vengono inseriti nelle tabelle custom_snapshots.MyOutputTable2 e 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>

Quando utilizzare elementi della raccolta separati

Nell'esempio precedente tutte le query vengono eseguite sullo stesso set di database. Per eseguire query differenti per diversi database è necessario creare un elemento della raccolta separato per ogni combinazione query-database.

Un altro scenario che richiede elementi della raccolta differenti è costituito da due database con lo stesso nome tabella ma con schema differente. Nell'esempio 4 sono illustrati gli elementi della raccolta per la gestione di tale scenario.

Esempio 4

Creare l'elemento della raccolta per il primo database, 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>

Creare l'elemento della raccolta per il secondo database, 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>

Elaborazione e output

I metadati dell'output della query dipendono dall'istruzione Transact-SQL specificata dall'utente, ma i mapping della colonna nell'origine o nella destinazione del flusso di dati vengono creati in fase di progettazione. Ciò vuol dire che ogni elemento della raccolta che utilizza questo tipo di agente di raccolta richiede un pacchetto personalizzato generato in fase di esecuzione e adattato al formato dello schema.

L'agente di raccolta dati utilizza un'attività personalizzata per creare un pacchetto di raccolta ed un pacchetto di caricamento quando viene ricevuto un nuovo elemento della raccolta o in presenza di aggiornamenti nei parametri di input relativi a un elemento della raccolta esistente. Questi pacchetti vengono salvati in msdb in modo tale da poter essere riutilizzati.

I comportamenti della raccolta e del caricamento di dati sono simili a quello descritto in Architettura ed elaborazione dell'agente di raccolta dati, ma vengono implementati in modo leggermente diverso per il tipo di agente di raccolta Query T-SQL generico.

Le tabelle del data warehouse di gestione seguenti vengono utilizzate per archiviare i dati raccolti dal tipo di agente di raccolta Query T-SQL generico:

  • core.snapshots - Questa tabella identifica ogni nuovo snapshot.

  • core.snapshot_timetable- L'ora dello snapshot viene archiviata in una tabella separata in quanto molti snapshot possono verificarsi quasi nello stesso momento.

  • core.source.info - Questa tabella archivia informazioni sull'origine dati nonché sui dati stessi.

Limitazioni

Vi sono alcune limitazioni da tenere presenti quando si utilizza il tipo di agente di raccolta Query T-SQL generico:

  • I nomi di colonna seguenti sono riservati all'agente di raccolta dati: snapshot_time, snapshot_id e database_name. Le tabelle create dai set di raccolta personalizzati non possono utilizzare questi nomi. Se si cerca di utilizzare tali nomi viene restituito un errore.

  • Una colonna di tipo sysname viene convertita in nvarchar(128) quando viene copiata nel data warehouse di gestione. Durante la raccolta dati SQL Server 2008 Integration Services (SSIS) converte i tipi di dati del database in tipi di dati SSIS (ad esempio sysname viene convertito in DT_WSTR e nvarchar(len) viene convertito in DT_WSTR). Tale conversione si verifica all'interno dell'attività flusso di dati di origine di OLE DB. Durante il caricamento di dati l'agente di raccolta legge dati dalla cache come dati di tipo SSIS e tali dati vengono considerati nvarchar(128), che dal punto di vista funzionale equivale a sysname.

  • Una colonna di tipo char(N) viene convertita in varchar(N) quando viene copiata nel data warehouse di gestione (char(N) può adattarsi in varchar(N)). Eccetto che per il fatto che la capacità di memorizzazione di char è fissa, mentre la capacità di memorizzazione varchar è variabile, questi tipi vengono considerati equivalenti dal punto di vista funzionale.

  • Una colonna di tipo varbinary viene convertita in binary quando viene copiata nel data warehouse di gestione.

  • Una colonna di tipo decimal viene convertita in numeric quando viene copiata nel data warehouse di gestione.

  • Una colonna di tipo nchar viene convertita in nvarchar quando viene copiata nel data warehouse di gestione.

  • Il tipo sqlvariant viene gestito dall'elaborazione predefinita per le colonne che non vengono gestite direttamente da SSIS. Ciò vuol dire che le colonne vengono considerate nvarchar(255), lasciando l'eventuale conversione al provider di dati.

    Nota

    In questo caso, la colonna viene creata con una lunghezza predefinita di 255 caratteri. Questo valore può tuttavia essere modificato in 4000 caratteri.

  • Tutte le colonne restituite da una query Transact-SQL devono avere un nome. Ad esempio select 1 non funzionerà, mentre funzionerà select 1 as one.

  • I tipi di dati seguenti non sono supportati da SSIS e non possono essere inclusi come colonne in nessuna tabella di output generata da un set di raccolta che utilizza il tipo di agente di raccolta Query T-SQL generico:

    • image

    • text

    • ntext

    • XML

  • Tutte le query eseguite dal tipo di agente di raccolta Query T-SQL generico devono restituire un solo set di risultati.

  • Le query delle tabelle temporanee locali sono supportate solo se prima vengono dichiarate come parte dello stesso batch. Le query delle tabelle temporanee globali sono interamente supportate.

  • Alle tabelle di destinazione del data warehouse di gestione non vengono trasmessi indici, chiavi private, chiavi esterne o altri vincoli. Questo perché gli stessi dati sono più volte oggetto di query e possono giungere da più computer in una stessa tabella.

  • Ogni altra limitazione relativa al tipo di query supportate che si applica all'attività flusso di dati di origine di OLE DB in SSIS si applica anche al tipo di agente di raccolta Query T-SQL generico.