DATA Step, Macro, Functions and more

Should &syscc be non-zero when this PROC SQL error occurs?

Accepted Solution Solved
Reply
Super Contributor
Posts: 387
Accepted Solution

Should &syscc be non-zero when this PROC SQL error occurs?

SAS 9.03.01M2P081512 on Windows

EG 6.1M1

 

Before I enter a TS track, I thought I'd check here first in case I'm missing something.

 

I checked the doc here and here:

 

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#p11nt7mv7k9hl4n1x...

http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#p1kxial1o2r732n1k3...

 

IMO this doc is a bit "generic" as to when SAS sets &syscc to non-zero.

 

This link is also relevant (ERRORCHECK option)

 

http://support.sas.com/documentation/cdl/en/lesysoptsref/69799/HTML/default/viewer.htm#n074sx2hkq8dz...

 

I'm allocating two librefs to SQL Server (this is output from a macro);

 

=================================================================================
LIBREF: CONTENT
CONNECT: NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXXXXX;Database=XXXXX;Trusted_Connection=yes;"
OPTIONS: bulkload=yes schema=content
=================================================================================

=================================================================================
LIBREF: TMP
CONNECT: NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXXXXX;Database=XXXXX;Trusted_Connection=yes;"
OPTIONS: bulkload=yes schema=tmp
=================================================================================

 

I submitted this code, which contains an error (mismatch between connect using and by):

 

 

proc sql;
connect using content;
execute(
SELECT * FROM INFORMATION_SCHEMA.Tables  /* not my real query */
) by tmp;
quit;
ERROR: The TMP engine cannot be found.
ERROR: A Connection to the tmp DBMS is not currently supported, or is not installed at your site.

 

I tried this using both errorcheck=normal | strict, with the same result, &syscc remains zero.  

 

I'm capturing the final &syscc in an audit table, so this result is misleading/incorrect.

 

Shouldn't SAS set &syscc to non-zero in this scenario?  If this is returning 0 as a return code to the O/S (i.e. %ERRORLEVEL%=0), this could be bad for scheduled jobs with upstream dependencies.


Accepted Solutions
Solution
‎01-19-2017 09:33 PM
Super User
Posts: 5,441

Re: Should &syscc be non-zero when this PROC SQL error occurs?

Posted in reply to ScottBass
From the documentation I would suspect that at least when ERRORCHECK=STRICT is set, syscc should not be zero. So a clarification from SAS Tech support would might be in place.
Data never sleeps

View solution in original post


All Replies
Solution
‎01-19-2017 09:33 PM
Super User
Posts: 5,441

Re: Should &syscc be non-zero when this PROC SQL error occurs?

Posted in reply to ScottBass
From the documentation I would suspect that at least when ERRORCHECK=STRICT is set, syscc should not be zero. So a clarification from SAS Tech support would might be in place.
Data never sleeps
Super User
Posts: 7,865

Re: Should &syscc be non-zero when this PROC SQL error occurs?

Posted in reply to ScottBass

Concur with @LinusH. As the ERROR clearly happens in the SAS context (and not in the remote execute), it should have influence on the overall return code, especially when run in batch. And there should at least be an option to "catch" errors that happen in the execute, if that does not happen already.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 125 views
  • 1 like
  • 3 in conversation