SAS/ACCESS Software for Relational Databases: Reference

Automatic Macro Variables

The following automatic macro variables are portable, but their values are determined by the SAS/ACCESS engine and your DBMS. Initially, the macro variables SYSDBMSG and SQLXMSG are blank, whereas SYSDBRC and SQLXRC are set to 0. Two of the macro variables that can be used anywhere while accessing DBMS data are SYSDBMSG and SYSDBRC.

SYSDBMSG: contains DBMS-specific error messages that are generated when you use SAS/ACCESS to access your DBMS data.
SYSDBRC: contains DBMS-specific error codes that are generated when you use SAS/ACCESS to access your DBMS data. Error codes that are returned are text, not numbers.

Because only one set of macro variables is provided, it is possible that, if tables from two different DBMSs are accessed, it might not be clear from which DBMS the error message originated. To address this problem, the name of the DBMS is inserted into the value of the SYSDBMSG macro variable.

For example, if you try to connect to ORACLE and use the incorrect password, you would receive the messages shown in SAS Log for an ORACLE Error.

SAS Log for an ORACLE Error

2? libname mydblib oracle user=pierre pass=paris path="orav7"; ERROR: ORACLE error trying to establish connection. ORACLE error is ORA-01017: invalid username/password; logon denied ERROR: Error in the LIBNAME or FILENAME statement. 3? %put &sysdbmsg; ORACLE: ORA-01017: invalid username/passsword; logon denied 4? %put &sysdbrc; -1017 5?

The contents of the SYSDBMSG and SYSDBRC macro variables can be printed in the SAS log by using the %PUT macro. The automatic macro variables SYSDBMSG and SYSDBRC are reset after each SAS/ACCESS LIBNAME statement, DATA step, or procedure has been executed.

The SQL Procedure Pass-Through Facility generates return codes and messages that are available to you through the following two SAS macro variables:

SQLXMSG: contains DBMS specific error messages. See SQL Procedure Pass-Through Facility Return Codes.
SQLXRC: contains DBMS specific error codes. See SQL Procedure Pass-Through Facility Return Codes.

SQLXMSG and SQLXRC can be used only with the SQL Procedure Pass-Through Facility.

The contents of the SQLXMSG and SQLXRC macro variables can be printed in the SAS log by using the %PUT macro. SQLXMSG is reset to a blank string and SQLXRC is reset to a "0" when any SQL Procedure Pass-Through statement is executed.

Chapter Contents
Previous
Next
Top of Page