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.

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

SettingsConfiguration Instructions
QueryType the SQL query in the Query field
System_CAPS_ICON_warning.jpg 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

SettingsConfiguration Instructions
Database typeSelect the Database type from the drop-down list. The options include the following:

- Access
- ODBC
- Oracle
- SQL Server
System_CAPS_ICON_important.jpg 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

SettingsConfiguration Instructions
FileType the name of the Access database file that you want to access.
Workgroup fileType the name of the Access workgroup file that is associated with this database.
User nameType the user name for the workgroup file.
PasswordType the password for the workgroup file.
DB passwordType the password for the Access database.

ODBC Connections

SettingsConfiguration Instructions
DSNEnter the data source name.
User nameEnter the user name for this database.
PasswordEnter the password for this database.

Oracle Connections

SettingsConfiguration Instructions
Service NameEnter the service name.
User nameEnter the user name for this database.
PasswordEnter the password for this database.

SQL Server Connections

SettingsConfiguration Instructions
AuthenticationSelect either Windows Authentication or SQL Server Authentication.
ServerEnter the name of the SQL Server that you want to access.
Initial catalogEnter 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

SettingsConfiguration Instructions
TimeoutEnter 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

SettingsConfiguration Instructions
Use the security of the account assigned to the serviceSelect this option if you want to run the Query Database activity using the same account that the runbook server uses.
This accountUse 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.

ItemDescription
Numeric return value of the queryWhen a query that returns a numeric value is used, this will be the value. For example, "Select COUNT(*) where FirstName=John"
Database queryThe database query that was sent to the database.
Initial CatalogThe 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 serverThe name of the database server. This published data will only be available when SQL Server is selected on the Connection tab.
Database userThe name of the user used to connect to the database server.
ODBC DSNThe name of the ODBC DSN. This published data will only be available when ODBC is selected on the Connection tab.
Oracle Service NameThe service name. This published data will only be available when Oracle is selected on the Connection tab.
Access fileThe Access database file that was queried. This published data will only be available when Access is selected on the Connection tab.
Access workgroup information fileThe 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
Show: