Using SQL, third-party applications can read data from the AR System  database. Similarly, both AR System client and server processes can read and write to external databases using SQL.

Accessing AR System data externally

Any process that has permission to query the database engine can read AR System  data. A third-party application writing to the AR System  database is not supported because there is no way to ensure data integrity. In addition, external applications reading AR System  data directly from the database are not subject to AR System  permissions, nor do they trigger any AR System  workflow. If this is not acceptable, data should be read through the Remedy AR System API.  For more information, see How the AR System database manages data.

Pulling data into 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, SQL*Plus for Oracle, 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.