Pulling data into BMC Remedy AR System with SQL
To pull information from external tables, you can use the Set Fields action with the Read Value for Field From field set to SQL. This allows you to send an SQL SELECT command to the database and assign the return values to AR System fields.
Observe the following general rules for using SQL commands:
- You need not use every value that is returned from the SQL command, but you must use at least one.
- You can use the same value in more than one field.
- You can issue only one SQL command per action. You cannot enter two commands separated by a semicolon and have both commands run. To run a set of commands, create separate actions, or create a stored procedure and run that. (Stored procedures do not return values.)
- Turn on AR System server SQL logging to resolve problems with the SQL syntax if it returns unexpected values or results. A good strategy is to start an SQL interpreter (for example, isql for Sybase, SQL*Plus for Oracle, Command Center for DB2, or Query Analyzer for Microsoft SQL Server) and to enter the same SQL command directly into the database to verify its validity.
- Because there is no error checking on the SQL statement, run the SQL statement directly against the database (as a test) before you enter it into the SQL Command field. You can then copy and paste the tested SQL command directly into the SQL Command field.
- If the SQL operation fails, an AR System error message and the underlying database error message appear.
For more information, see Set Fields action and structures.