Query Database
Updated: May 13, 2016
Applies To: System Center 2012 SP1 - Orchestrator, System Center 2012 - Orchestrator, System Center 2012 R2 Orchestrator
The Query Database activity queries a database and returns the resulting rows as published data. This activity supports the following database types:
Access
ODBC
Oracle
SQL Server
The Query Database activity can be used to query a database for the detailed description of an error code that has appeared on one of the systems in the data center and then that description is sent to an administrator in an email message.
Configuring the Query Database Activity
Before you configure the Query Database activity you will need to determine the following:
The database that you are connecting to
The SQL query that you are running
Use the following information to configure the Query Database activity.
Details
| Settings | Configuration Instructions |
|---|---|
| Query | Type the SQL query in the Query field |
Warning
The Query Database activity does not support queries that return data as XML, such as queries that use the FOR XML clause in SQL Server.
Connection
| Settings | Configuration Instructions |
|---|---|
| Database type | Select the Database type from the drop-down list. The options include the following: - Access - ODBC - Oracle - SQL Server |
Important
When Orchestrator is installed on a non-English operating system, and you set the Connection for Database type to SQL Server, the Server input value cannot be localhost. You must use the actual computer name.
Configuration instructions for each Connection tab Database type are listed in the following tables.
Access Connections
| Settings | Configuration Instructions |
|---|---|
| File | Type the name of the Access database file that you want to access. |
| Workgroup file | Type the name of the Access workgroup file that is associated with this database. |
| User name | Type the user name for the workgroup file. |
| Password | Type the password for the workgroup file. |
| DB password | Type the password for the Access database. |
ODBC Connections
| Settings | Configuration Instructions |
|---|---|
| DSN | Enter the data source name. |
| User name | Enter the user name for this database. |
| Password | Enter the password for this database. |
Oracle Connections
| Settings | Configuration Instructions |
|---|---|
| Service Name | Enter the service name. |
| User name | Enter the user name for this database. |
| Password | Enter the password for this database. |
SQL Server Connections
| Settings | Configuration Instructions |
|---|---|
| Authentication | Select either Windows Authentication or SQL Server Authentication. |
| Server | Enter the name of the SQL Server that you want to access. |
| Initial catalog | Enter the name of the initial catalog. If you selected the SQL Server Authentication option, type the user name and password used to access the SQL Server in the User name and Password boxes. |
Timeout
| Settings | Configuration Instructions |
|---|---|
| Timeout | Enter the amount of time that the Query Database activity will wait for the database operation to complete. Set this value to 0 to wait indefinitely. |
Security Credentials
| Settings | Configuration Instructions |
|---|---|
| Use the security of the account assigned to the service | Select this option if you want to run the Query Database activity using the same account that the runbook server uses. |
| This account | Use this option to specify a different account. Enter the User name and Password. Note: If you specify an invalid user name or password, the account assigned to the runbook server will be used to run the activity. |
Published Data
The following table lists the published data items.
| Item | Description |
|---|---|
| Numeric return value of the query | When a query that returns a numeric value is used, this will be the value. For example, "Select COUNT(*) where FirstName=John" |
| Database query | The database query that was sent to the database. |
| Initial Catalog | The initial catalog that was used when connecting to the database. This published data will only be available when SQL Server is selected on the Connection tab. |
| Database server | The name of the database server. This published data will only be available when SQL Server is selected on the Connection tab. |
| Database user | The name of the user used to connect to the database server. |
| ODBC DSN | The name of the ODBC DSN. This published data will only be available when ODBC is selected on the Connection tab. |
| Oracle Service Name | The service name. This published data will only be available when Oracle is selected on the Connection tab. |
| Access file | The Access database file that was queried. This published data will only be available when Access is selected on the Connection tab. |
| Access workgroup information file | The Access workgroup file that is associated with the Access database file. This published xata will only be available when Access is selected on the Connection tab. |
| For each row published | |
| Full line as a string with fields separated by ; | The entire the row that was published with each field in the row separated by a semi-colon (;). Use the Field data manipulation function to obtain the values of a field within the row |