SAS/ACCESS^® software takes advantage of database management system (DBMS) capabilities by passing certain SQL operations to the DBMS whenever possible. This function can reduce data movement and therefore improve performance. The performance impact can be significant when you access large DBMS tables and the SQL query that is passed to the DBMS subsets the table to reduce the number of rows. SAS/ACCESS software sends operations to the DBMS for processing in these situations.

When you use the SAS/ACCESS LIBNAME statement, you submit SAS statements that SAS/ACCESS software can often translate into the SQL of the DBMS and then pass to the DBMS for processing. With the automatic translation abilities of SAS/ACCESS software, you can often achieve performance benefits without needing to write DBMS-specific SQL code.

SQLIPONEATTEMPT System Option

Typically, the SQL query that SAS generates for the DBMS is highly optimized. However, in cases when SQL query generation is partial (or not optimal), the partial resulting data is read into SAS, where the remainder of the SQL query is processed. The SQLIPONEATTEMPT option specifies that if the first attempt to optimize the SQL query is not acceptable to the DBMS, no further attempts are made. Then the log shows a SQL procedure error, as shown here:

You can obtain more information in the SAS log when you add the SAS system options SQL_IP_TRACE and MSGLEVEL=I, as shown here:

The resulting log information looks similar to this:

SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT Environment Variable

There might also be situations where the highly optimized SQL query is passed to the database, but an error occurs during processing. For example, an out-of-space condition occurs. In this case, SAS/ACCESS software might attempt to rewrite the query and send it partially to the database. Setting the environment variable SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT to YES or Y prevents the rewrite of the query.

The environment variable can enable you to control what happens when an execution-type error occurs. The case-sensitive environment variable can be set as follows:

You must also use the SAS system option DBIDIRECTEXEC. To enable the additional execution or run-time checking, use this code:

The SQLIPONEATTEMPT option is not required when using SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT=YES. 

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M6 is available at:

A fix for this issue for Base SAS 9.4_M5 is available at: