Performing the SQL operation

Observe the following general rules for using SQL commands:

• You need not use every value that is returned from the SQL command. If you do not use any values from the SQL command, the Set Fields action acts like the default selection CURRENT SCREEN (active links) or CURRENT TRANSACTION (filters or escalation), and the system removes your SQL query.
• 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. However, stored procedures do not return values.
• Turn on BMC Remedy AR System server SQL logging to debug the SQL syntax if it returns unexpected values or results. A good debugging strategy is to start an SQL interpreter (for example, SQL*Plus for Oracle, Command Center for Microsoft ISQL/w 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, a BMC Remedy AR System error message and the underlying database error message appear.
• You can affect database performance by how an SQL query is written. If the row has many columns, a *SELECT ** SQL command can have a greater performance impact than if you select specific columns. For more information, see your relational database documentation.

Database security issues

To use SQL commands, familiarize yourself with the features of your underlying database. All SQL commands are issued by the following users:

• For Oracle, Microsoft SQL Server databases, the ARAdmin user (or the AR System Database User defined during installation).

Depending on which database you are using, the data must be accessible to the user issuing the command. If you are running BMC Remedy AR System as one of these users without permission to access the database, you cannot issue the SQL command.

To access databases other than BMC Remedy AR System server databases, use the database name as part of the SQL command syntax, for example, using MS SQL: