Automatic Macro Variables for Relational Databases

[Justin]

SYSDBMSG, SYSDBRC, SQLXMSG, and SQLXRC are automatic SAS macro variables. The SAS/ACCESS engine and your DBMS determine their values. Initially, SYSDBMSG and SQLXMSG are blank, and SYSDBRC and SQLXRC are set to 0.

SAS/ACCESS generates several return codes and error messages while it processes your programs. This information is available to you through these SAS macro variables.

SYSDBMSG: contains DBMS-specific error messages that are generated when you use SAS/ACCESS software to access your DBMS data.

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

You can use these variables anywhere while you are accessing DBMS data. 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 at the beginning of the SYSDBMSG macro variable message or value. The contents of the SYSDBMSG and SYSDBRC macro variables can be printed in the SAS log by using the %PUT macro. They are reset after each SAS/ACCESS LIBNAME statement, DATA step, or procedure is executed.

In the statement below, %SUPERQ masks special characters such as &, %, and any unbalanced parentheses or quotation marks that might exist in the text stored in the SYSDBMSG macro.

%put %superq(SYSDBMSG)

These special characters can cause unpredictable results if you use this statement:

%put &SYSDBMSG

It is more advantageous to use %SUPERQ.

If you try to connect to Oracle and use the incorrect password, you receive the messages shown in this output.

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 %superq(sysdbmsg);
 Oracle: ORA-01017: invalid username/passsword; logon denied
  4? %put &sysdbrc;
 -1017
  5?

You can also use SYMGET to retrieve error messages:

msg=symget("SYSDBMSG");  For example: 
 data_null_;
 msg=symget("SYSDBMSG");
 put msg;
 run;

The SQL pass-through facility generates return codes and error messages that are available to you through these SAS macro variables:

SQLXMSG: contains DBMS-specific error messages.

SQLXRC: contains DBMS-specific error codes.

You can use SQLXMSG and SQLXRC only through explicit pass-through with the SQL pass-through facility. See Return Codes.

You can print the contents of SQLXMSG and SQLXRC in the SAS log by using the %PUT macro. SQLXMSG is reset to a blank string, and SQLXRC is reset to 0 when any SQL pass-through facility statement is executed.

• This discussion was modified 3 years, 9 months ago by  Justin.