Share via

  • Article

Generic T-SQL Query Collector Type

The Generic T-SQL Query collector type executes a user-provided Transact-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse. This collector type is registered in the core.supported_collector_types view in the management data warehouse.

This collector has the following input parameters:

  • Value - The Transact-SQL query. You can provide more than one query as input.

  • OutputTable - Provide the name of the table for saving query results before they are uploaded to the management data warehouse.

  • Database - Specify the database or databases to run the query against. You can specify databases by name or use the * wildcard character to specify all the databases on the server. If you do not provide a value for Database, the query is run against all the system databases.

Generic T-SQL Query Input Schema

The schema for Generic T-SQL Query collector input is as follows.

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

The following code examples show how the schema can be used. For a full code sample, see How to: Create a Set with a Generic T-SQL Query Collector Type.

Example 1

The following example queries the system and user databases, and then puts the results in the custom_snapshots.VerifyDbName table.

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

Example 2

The following example queries the three system databases and puts the results in the custom_snapshots.MyOutputTable1 table.

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

Example 3

The following example queries only the user databases and puts the results in the custom_snapshots.MyOutputTable2 and custom_snapshots.MyOutputTable3 tables.

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

When to Use Separate Collection Items

In the preceding example, all the queries are executed against the same set of databases. If you need to execute different queries for different databases, you must create a separate collection item for each query-database combination.

Another scenario that requires different collection items is two databases with the same table name, but different schema. Example 4 shows the collection items for handling this scenario.

Example 4

Create the collection item for the first 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>

Create the collection item for the second 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>

Processing and Output

The metadata of the query output depends on the Transact-SQL statement specified by the user, but the column mappings in the data flow source or destination is created at design time. This means that every collection item that uses this collector type requires a customized package generated at run time and tailored to the format of its schema.

The data collector uses a custom task to create a collection package and an upload package when a new collection item is received, or if there is an update in the input parameters for an existing collection item. These packages are saved in msdb so they can be reused.

Data collection and upload behavior are similar to that described in Data Collector Architecture and Processing, but implemented slightly differently for the Generic T-SQL Query collector type.

The following management data warehouse tables are used to store data collected by the Generic T-SQL Query collector type:

  • core.snapshots - This table identifies each new snapshot.

  • core.snapshot_timetable - The snapshot time is stored in a separate table, because many snapshots can happen at nearly the same time.

  • core.source.info - This table stores information about the data source as well as the data.

Limitations

There are certain limitations to be aware of when using the Generic T-SQL Query collector type:

  • The following column names are reserved for the data collector: snapshot_time, snapshot_id and database_name. No tables created by custom collection sets can use these names. An error is returned if an attempt is made to use them.

  • A column of type sysname is converted to nvarchar(128) when it is copied to the management data warehouse. During data collection, SQL Server 2008 Integration Services (SSIS) converts database data types to SSIS data types (for example, sysname becomes DT_WSTR, and nvarchar(len) becomes DT_WSTR). This conversion happens inside the OLE DB source data flow task. During data upload, the data collector reads data from the cache as an SSIS data type and the data is treated as nvarchar(128), which is functionally equivalent to sysname.

  • A column that is of type char(N) becomes varchar(N) when copied to the management data warehouse (char(N) can fit in varchar(N)). Except for the fact that char storage size is fixed, whereas varchar storage size is variable, these types are treated as functionally equivalent.

  • A column that is of type varbinary becomes binary when copied to the management data warehouse.

  • A column that is of type decimal becomes numeric when copied to the management data warehouse.

  • A column that is of type nchar becomes nvarchar when copied to the management data warehouse.

  • The sqlvariant type gets handled by the default processing for columns that SSIS does not directly handle. This means that the columns are treated as nvarchar(255), leaving any conversion for the data provider.

    Note

    In this case, the column is created with a default length of 255 characters. However, it can be changed to 4000 characters.

  • All columns returned by a Transact-SQL query must have a name. For example, select 1 will not work, but select 1 as one will.

  • The following data types are not supported by SSIS and cannot be included as columns in any output table generated by a collection set that uses the Generic T-SQL Query collector type:

    • image

    • text

    • ntext

    • XML

  • All queries executed by the Generic T-SQL Query collector type must return a single result set.

  • Local temp table queries are only supported if they are first declared as part of the same batch. Global temp table queries are fully supported.

  • No indexes, private keys, foreign keys, or any other constraints are carried to the target tables in the management data warehouse. This is because the same data is queried multiple times, and the data can be coming from multiple computers to a single table.

  • Any other limitations regarding the type of supported queries that apply to the OLE DB source data flow task in SSIS also apply to the Generic T-SQL Query collector type.