The setting for the READBUFF= option is not honored in the following situation:

• In SAS/ACCESS Interface to Microsoft SQL Server, you assign a libref and submit it in combination with the READBUFF= option.
• You then use the SQL procedure's Pass-Through facility against the libref.

The READBUFF= option, which enables buffered data reads, is specified either in the LIBNAME statement or as a SAS data set option. As a result of internal processing, SAS/ACCESS^® Interface to ODBC cannot always perform buffered reads with PROC SQL queries. While no errors are generated in this situation, performance can suffer.

Queries that contain ORDER BY and GROUP BY clauses fall into the category of SQL queries that, when included in PROC SQL pass-through code, cause the software not to honor the BUFFSIZE= setting.

You can use the SASTRACE= option to check the number of rows that are read into the SAS^® System per each fetch operation and to verify whether the BUFFSIZE option setting is being honored. For example, if you submit the following OPTIONS statement with the SASTRACE= option at the beginning of your code, the results will be written to the SAS log:

This code yields messages such as the following in the SAS log:

ODBC: Fetch time in seconds for 1 row is 0.000005

This particular message indicates that one row is read at a time, which means means that the buffered-read capability is disabled.

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

Operating System and Release Information

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at: